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)