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)

Wildhoney · 09-07-2007, 12:24 PM

Isn't it @myVar:= and not $v1:=? In any case, I do like the way you do it for the sub-selects, but I also like the way variables can make things much more readable.

For instance:

Code:

SELECT
	@count:=COUNT(*)
FROM
	myTable
HAVING
	@count > 1

Obviously this is a useless example but it's how I use the variables for on such statements as inner joins where the query can get somewhat complex.

Karl · 09-07-2007, 12:34 PM

Nice post Bluesaga, good to see people contributing to the MySQL forum :)

bluesaga · 09-07-2007, 02:25 PM

Thanks for the kind words Karl.

And thanks for the typo-catch Adam!

However for your peice of code, why wouldn't you just do:

Code:

SELECT
      count(*) as count
FROM
      myTable
HAVING
      count > 1

More efficient than loading the variable into memory.