Pythian Group

Last month at the Boston MySQL User Group, I went through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. I also explained how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries were explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides in the video — due to error correction) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf.

Here’s the video:
Read the rest of this entry . . .

One of my colleagues made a typo in a query today that led to me discovering this issue — I think it’s a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (fid tinyint unsigned not null primary key); create table bar (bid char(1)

not null default ”);

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo (fid) VALUES (1),(2); insert into bar (bid) VALUES (’1′),(‘a’);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0
Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0
mysql> explain select * from foo where fid in (select fid from bar)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: foo

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 1

          ref: NULL

         rows: 2

        Extra: Using where; Using index

*************************** 2. row ***************************

           id: 2

  select_type: DEPENDENT SUBQUERY

        table: bar

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2

        Extra: Using where

2 rows in set (0.00 sec)
mysql> select * from foo where fid in (select fid from bar);

+—–+

| fid |

+—–+

|   1 |

|   2 |

+—–+

2 rows in set (0.00 sec)
mysql> select fid from bar;

ERROR 1054 (42S22): Unknown column ‘fid’ in ‘field list’

mysql>

Part of me thinks there’s an implicit join going on or something, but I’m not sure how that’s working.

So, should select * from foo where fid in (select fid from bar); work? If so, why is that?