Australia photo & PC memo

SELECT column_a FROM table_a WHERE id IN (SELECT table_a_id FROM table_b);
SELECT DISTINCT column_a FROM table_a, table_b WHERE table_a.id = table_b.table_a_id;

SELECT column_a FROM table_a WHERE id NOT IN (SELECT table_a_id FROM table_b);
SELECT column_a FROM table_a LEFT JOIN table_b ON table_a.id = table_b.table_a_id WHERE table_b.table_a_id IS NULL;

MySQL does not allow to use a table in a subquery if that table is going to be updated.

MySQL AB :: MySQL 5.1 Reference Manual :: 1.8.5 MySQL Differences from Standard SQL

1.8.5.1. SELECT INTO TABLE
1.8.5.2. Transactions and Atomic Operations
1.8.5.3. Stored Routines and Triggers
1.8.5.4. Foreign Keys
1.8.5.5. Views
1.8.5.6. '--' as the Start of a Comment

MySQL AB :: MySQL Presentations: Optimizing MySQL

* SELECT SQL_BUFFER_RESULTS ...
Will force MySQL to make a temporary result set. As soon as the temporary set is done, all locks on the tables are released. This can help when you get a problem with table locks or when it takes a long time to transfer the result to the client.

* SELECT SQL_SMALL_RESULT ... GROUP BY ...
To tell the optimizer that the result set will only contain a few rows.

* SELECT SQL_BIG_RESULT ... GROUP BY ...
To tell the optimizer that the result set will contain many rows.

* SELECT STRAIGHT_JOIN ...
Forces the optimizer to join the tables in the order in which they are listed in the FROM clause.

* SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
Forces MySQL to use/ignore the listed indexes.