Subqueries 3: They are tables in MySQL

http://dev.mysql.com/tech-resources/articles/subqueries_part_1.html

Subqueries in MySQL, Part 1

Pretending to be a Table

Subqueries in the FROM clause (a.k.a. "derived tables") are one of the most useful features of the new subquery support. Essentially, it's simply a subquery that you are allowed to put in the FROM part of a query. When MySQL processes the query it pretends that the result of the subquery is actually another table. First, a simple example, to show you what I mean:

  SELECT foo
  FROM (SELECT 1 AS foo) AS tbl;

  +-----+
  | foo |
  +-----+
  |   1 |
  +-----+

You'll notice the part at the end: FROM (SELECT 1 AS foo) AS tbl is put where you would normally find a list of tables. You'll also notice that I have aliased the subquery (the part in the parenthesis) with AS tbl. All subqueries in the FROM clause must be aliased to something, anything, otherwise you'll get an error. Every table must have a name, and this subquery is no exception.

2
http://zetcode.com/databases/mysqltutorial/subqueries/

INSERT INTO Cars2 SELECT * FROM Cars;

This is a simple subquery. We insert all rows from the Cars table into the Cars2 table.

3

SELECT Name FROM Customers WHERE CustomerId IN    
    -> (SELECT DISTINCT CustomerId FROM Reservations);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

The above query returns the names of the customers, who made some reservations. The inner query returns customer Ids from the Reservations table. We use the IN predicate to select those names of customers, who have their CustomerId returned from the inner select query.

mysql> SELECT DISTINCT Name FROM Customers JOIN Reservations
    -> ON Customers.CustomerId=Reservations.CustomerId;
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

The previous subquery can be rewritten using SQL join.

3 just JOIN clause (inner join?) in mySql = ???




4

Correlated subqueries

A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The subquery is evaluated once for each row processed by the outer query.

SELECT Name FROM Cars WHERE Cost <
    -> (SELECT AVG(Cost) FROM Cars);

5 EXISTS

Subqueries with EXISTS, NOT EXISTS

If a subquery returns any values, then the predicate EXISTS returns TRUE. And NOT EXISTS FALSE.

mysql> SELECT Name FROM Customers WHERE EXISTS
    -> (SELECT * FROM Reservations WHERE
    -> Customers.CustomerId=Reservations.CustomerId);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

In the above SQL statement we select all customers' names, which have an entry in the Reservations table.

mysql> SELECT Name FROM Customers WHERE NOT EXISTS    
    -> (SELECT * FROM Reservations WHERE 
    -> Customers.CustomerId=Reservations.CustomerId);
+------------+
| Name       |
+------------+
| Tom Willis |
+------------+

In this query, we return all customers that do not have an entry in the Reservations table. Both SQL queries are correlated queries.