| Lesson 5 | An introduction to Subselect statements |
| Objective | Sub-SELECT statements |
There are times when you may want to select values from one table and use values from another table as a guide.
For example, you may want to select values from a customer table when the customer ID exists in another table. In this case, you could use a sub-SELECT statement.
This may sound a bit familiar because the concept resembles joins, which were covered in Intro to SQL Part I,
Sub-SELECTS are very similar, but they are more flexible than joins.
To see this, take a look at the following statement:For example, you may want to select values from a customer table when the customer ID exists in another table. In this case, you could use a sub-SELECT statement.
This may sound a bit familiar because the concept resembles joins, which were covered in Intro to SQL Part I,
Sub-SELECTS are very similar, but they are more flexible than joins.
What is the purpose of Sub-Select Statements?
SELECT Title FROM Titles WHERE pub_id IN (SELECT Pub_ID FROM Publishers WHERE State='CA')
This uses the sub-SELECT statement to relate the Titles table to the Publishers table.
The difference is that you only want to retrieve the rows in which the State column is "CA".
This filter determines what publisher IDs will be used to select rows from the Titles table.
Remember, with a join, you end up pulling all matching rows from two or more tables. You don't filter rows on the fly as easily with the join.
The difference is that you only want to retrieve the rows in which the State column is "CA".
This filter determines what publisher IDs will be used to select rows from the Titles table.
Remember, with a join, you end up pulling all matching rows from two or more tables. You don't filter rows on the fly as easily with the join.