The art of sub-selects

bluesaga · 09-07-2007, 10:27 AM

Sub-Selects are one of the most powerful new(ish) additions to the mysql database. They are supported from mysql 4.1 onward.

Code:

SET @v1 = '';
SELECT @v1:=column_id FROM table1 WHERE column='blah';
SELECT * FROM table2 WHERE column2 = @v1;

Now while thats all well and good, its not very practical, long and doesnt look very nice. Now lets take doing the same thing with a mysql sub-select!

Code:

SELECT * FROM table2 WHERE column2 = (SELECT column_id FROM table1 WHERE column='blah' LIMIT 1);

See how easy that is!

Now for some more advanced stuff:

Using a sub-select query as a table:

Code:

SELECT 
     table1.id, 
     tbl2.example 
FROM 
     table1, 
     (SELECT * FROM table2 WHERE id=1) tbl2
WHERE
     table1.referance = table2.referance

The query above, uses a query result set to create a temporary table in mysql, you can then browse this table using normal functions, extremely handy in some cases.

Using Sub-select in an "IN" where statement:

Code:

SELECT
     column
FROM
     table1
WHERE
     table1.ref_id IN (SELECT id FROM table2)

The subselect query in this case acts as if you were adding a list to the IN, ie:

Code:

table1.ref_id IN (1,2,3,4,5,6,7,8,9,10)