TalkPHP - The art of sub-selects

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - The art of sub-selects (http://www.talkphp.com/mysql-databases/1036-art-sub-selects.html)

The art of sub-selects

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)

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.

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

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.