network servers on an enclosure

Subqueries in SQL

Leave a reply
Advertisements

Subqueries allow you to use the tables produced by SELECT queries. One advantage of this is instead of referencing an existing table in your database you can pull from the table you are making when making your SQL query. This is best explained with an example.

WHERE Clause

ad

I have two tables I want to pull data from called salespeople and dealerships. The titles of these two tables explain what they contain. One column these two tables have in common is dealership_id which identifies the dealership in the dealership table and where the salesperson works at in the salespeople table.

Now the question we want to know is “Which of my salespeople work in Texas.” This can only be determined by using my two tables together the salespeople table does not have the state the person is in while the dealerships table has the state but does not have the salesperson’s information.

This problem can be solved with a subquery. Below is the code followed by the output and an explanation.

SELECT *
FROM salespeople
WHERE dealership_id IN (
	SELECT dealership_id FROM dealerships
	WHERE dealerships.state = 'TX'
	)

The first two lines are standard SQL coding. In line three, we have our subquery in the WHERE clause. What is happening is that we are filtering the data to include only dealership_id that matches the state of Texas inside the parentheses. This leads to a note that subqueries are always inside parentheses.

SELECT Clause

Using subqueries in the WHERE clause is most common but we can also do them in the SELECT clause as well. In our first example we learned where the employees work but let’s say we want to know the city and not just the state. Below is a way to pull the city data for one dealership with the salespeople data.

SELECT *,       
       (SELECT city 
        FROM dealerships d         
        WHERE d.dealership_id = 19) as City 
FROM salespeople
WHERE salespeople.dealership_id =19;

In this example, we pull all data from salespeople while pulling only data related to the city from the dealership while also filtering for only dealership_id 19.

FROM Clause

We can also place subqueries in a FROM clause. In the example below, I want the first and last name of the salesperson followed by the city and state they work in. The name info is on the salespeople table and the city and state are on the dealership table. Below is the code and results.

SELECT * 
FROM 
   (SELECT s.first_name, s.last_name,d.city,d.state
                 FROM salespeople s , dealerships d 
                 WHERE s.dealership_id= d.dealership_id);

The code should be self-explanatory. Inside the parentheses, we are creating a table we want to pull data from. The subquery is essentially a join of the two tables based on the criteria. This brings up an important point. Subqueries and joins (inner joins in particular) serve the same purpose. Joins are better for large amounts of data while suqueries are better for smaller amounts of data.

Conclusion

Subqueries are another tool that can be used to deal with data questions. They can be used in the WHERE, SELECT, or FROM clauses. When to use them is a matter of preference and efficiency.

Leave a ReplyCancel reply