SQL FROM Clause ¶

The FROM clause specify which tables we are selecting data from.

• From the table "customer"

[...] FROM customer [...]

• From the cartesian product of "c1" and "c2".

[...] FROM c1, c2  [...]

In many scenarios, you will select more than one table. If these tables share attributes with the same name, we need to prefix them.

SELECT xxx.* FROM xxx [...]; -- table prefixed
SELECT x.* FROM xxx x [...]; -- alias prefixed

For instance:

SELECT c.id, p.id FROM Customer c NATURAL JOIN Purchase p;

Let's say we want to link (join) customers to their purchases.

Notice the c_id column with an ID linking products to a customer.

☠️ If the join fails, e.g., there is no link, the cartesian product is done instead (each row is linked to every other row).

🚀 Join clauses can be chained, and you can mix multiple of them.

NATURAL JOIN ¶

Cartesian product based on the columns having the same name.

SELECT p_id, name FROM customer NATURAL JOIN purchase

The c_id column is the only one present in both tables. The result is [(1, 'Luna'), (2, 'Luna')].

🔥️ Because the join is not explicit, you may have unexpected results.

JOIN on an attribute ¶

The Equi-join is an explicit NATURAL JOIN. The second form can be used when columns have different names.

-- can be used on multiple columns with ","
[...] FROM customer JOIN purchase USING (c_id)
[...] FROM customer c JOIN purchase p ON c.c_id = p.c_id

OUTER JOIN ¶

Until now, the row "(2, Henry, ...)" was never shown, because there was no "c_id=2" in purchases. You can show such records using OUTER JOIN.

/* p.c_id can be null */
[...] FROM customer c LEFT OUTER JOIN purchase p ON c.c_id = p.c_id
/* c.c_id can be null */
[...] FROM customer c RIGHT OUTER JOIN purchase p ON c.c_id = p.c_id
/* both can be null */
[...] FROM customer c FULL OUTER JOIN purchase p ON c.c_id = p.c_id