Join operations take two relations and return as a result another relation. Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated. For USING(a,b,..), the attributes a,b,... should be present in both relations. However, for ON, we can compare any two attributes if they are comparable. USING needs to have identical names for matched columns in both tables. This is not the case for ON. USING(a) means the attribute name 'a' appears only once. When we use ON, then there is a repetition in column names. A USING clause can be rewritten as an ON clause that compares corresponding columns. However, although USING and ON are similar, they are not quite the same. Consider the following two queries: a LEFT JOIN b USING (c1, c2, c3) a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 To determine which rows satisfy the join condition, both joins are semantically identical. To determine which columns to display for SELECT * expression, the two joins are not semantically identical. JOIN has higher precedence than the comma operator (,), so the join expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not as ((t1, t2) JOIN t3). **************************************************************** TABLE CREATION **************************************************************** CREATE TABLE tabnatural1(b VARCHAR(10), a INT); CREATE TABLE tabnatural2(a INT, c VARCHAR(10)); INSERT INTO tabnatural1 VALUES('x', 1); INSERT INTO tabnatural1 VALUES('y', 2); --------- | b | a | --------- | x | 1 | | y | 2 | --------- INSERT INTO tabnatural2 VALUES(2, 'z'); INSERT INTO tabnatural2 VALUES(3, 'w'); --------- | a | c | --------- | 2 | z | | 3 | w | --------- **************************************************************** **************************************************************** **************************************************************** , / CROSS JOIN / INNER JOIN / JOIN All are equivalent without USING / ON **************************************************************** SELECT * FROM tabnatural1 CROSS JOIN tabnatural2; or SELECT * FROM tabnatural1 INNER JOIN tabnatural2; or SELECT * FROM tabnatural1 JOIN tabnatural2; or SELECT * FROM tabnatural1, tabnatural2; ------------------ | b | a | a | c | ------------------ | y | 2 | 2 | z | | x | 1 | 2 | z | | y | 2 | 3 | w | | x | 1 | 3 | w | ------------------ SELECT * FROM tabnatural2, tabnatural1; or SELECT * FROM tabnatural2 CROSS JOIN tabnatural1; or SELECT * FROM tabnatural2 INNER JOIN tabnatural1; or SELECT * FROM tabnatural2 JOIN tabnatural1; ------------------ | a | c | b | a | ------------------ | 3 | w | x | 1 | | 2 | z | x | 1 | | 3 | w | y | 2 | | 2 | z | y | 2 | ------------------ **************************************************************** **************************************************************** **************************************************************** NATURAL JOIN / NATURAL INNER JOIN Cannot use USING / ON with NATURAL JOIN / NATURAL INNER JOIN Can use WHERE with NATURAL JOIN / NATURAL INNER JOIN **************************************************************** SELECT * FROM tabnatural1 NATURAL JOIN tabnatural2; or SELECT * FROM tabnatural1 NATURAL INNER JOIN tabnatural2; ------------- | a | b | c | ------------- | 2 | y | z | ------------- SELECT * FROM tabnatural2 NATURAL JOIN tabnatural1; or SELECT * FROM tabnatural2 NATURAL INNER JOIN tabnatural1; ------------- | a | c | b | ------------- | 2 | z | y | ------------- **************************************************************** **************************************************************** **************************************************************** **************************************************************** SELECT * FROM tabnatural1 NATURAL JOIN tabnatural2; is equivalent to SELECT * FROM tabnatural1 CROSS JOIN tabnatural2 USING (a); is equivalent to SELECT * FROM tabnatural1 INNER JOIN tabnatural2 USING (a); is equivalent to SELECT * FROM tabnatural1 JOIN tabnatural2 USING (a); if tabnatural1 and tabnatural2 has only one common atribute named a ------------- | a | b | c | ------------- | 2 | y | z | ------------- With , we cannot use USING / ON **************************************************************** **************************************************************** **************************************************************** **************************************************************** SELECT * FROM tabnatural1 CROSS JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a; is equivalent to SELECT * FROM tabnatural1 INNER JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a; is equivalent to SELECT * FROM tabnatural1 JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a; ----------------- | b | a | a | c | ----------------- | y | 2 | 2 | z | ----------------- SELECT * FROM tabnatural2 CROSS JOIN tabnatural1 ON tabnatural2.a = tabnatural1.a is equivalent to SELECT * FROM tabnatural2 INNER JOIN tabnatural1 ON tabnatural2.a = tabnatural1.a is equivalent to SELECT * FROM tabnatural2 JOIN tabnatural1 ON tabnatural2.a = tabnatural1.a ----------------- | a | c | b | a | ----------------- | 2 | z | y | 2 | ----------------- **************************************************************** **************************************************************** **************************************************************** **************************************************************** SELECT * FROM tabnatural1 NATURAL LEFT OUTER JOIN tabnatural2; or SELECT * FROM tabnatural1 LEFT OUTER JOIN tabnatural2 USING(a); if tabnatural1 and tabnatural2 has only one common atribute named a ---------------- | a | b | c | ---------------- | 1 | x | NULL | | 2 | y | z | ---------------- SELECT * FROM tabnatural2 NATURAL LEFT OUTER JOIN tabnatural1; or SELECT * FROM tabnatural2 LEFT OUTER JOIN tabnatural1 USING(a); if tabnatural1 and tabnatural2 has only one common atribute named a ---------------- | a | c | b | ---------------- | 2 | z | y | | 3 | w | NULL | ---------------- SELECT * FROM tabnatural1 NATURAL RIGHT OUTER JOIN tabnatural2; or SELECT * FROM tabnatural1 RIGHT OUTER JOIN tabnatural2 USING(a); if tabnatural1 and tabnatural2 has only one common atribute named a ---------------- | a | c | b | ---------------- | 2 | z | y | | 3 | w | NULL | ---------------- SELECT * FROM tabnatural2 NATURAL RIGHT OUTER JOIN tabnatural1; or SELECT * FROM tabnatural2 RIGHT OUTER JOIN tabnatural1 USING(a); if tabnatural1 and tabnatural2 has only one common atribute named a ---------------- | a | b | c | ---------------- | 1 | x | NULL | | 2 | y | z | ---------------- **************************************************************** **************************************************************** **************************************************************** LEFT OUTER JOIN / LEFT JOIN RIGHT OUTER JOIN / RIGHT JOIN **************************************************************** SELECT * FROM tabnatural1 LEFT OUTER JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a; ----------------------- | b | a | a | c | ----------------------- | x | 1 | NULL | NULL | | y | 2 | 2 | z | ----------------------- SELECT * FROM tabnatural2 LEFT OUTER JOIN tabnatural1 ON tabnatural2.a = tabnatural1.a; ----------------------- | a | c | b | a | ----------------------- | 2 | z | y | 2 | | 3 | w | NULL | NULL | ----------------------- SELECT * FROM tabnatural1 RIGHT OUTER JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a; ----------------------- | b | a | a | c | ----------------------- | y | 2 | 2 | z | | NULL | NULL | 3 | w | ----------------------- SELECT * FROM tabnatural2 RIGHT OUTER JOIN tabnatural1 ON tabnatural2.a = tabnatural1.a; ----------------------- | a | c | b | a | ----------------------- | NULL | NULL | x | 1 | | 2 | z | y | 2 | ----------------------- **************************************************************** **************************************************************** **************************************************************** FULL OUTER JOIN (Not in Mysql) **************************************************************** (SELECT a,b,c FROM tabnatural1 NATURAL LEFT OUTER JOIN tabnatural2) UNION (SELECT a,b,c FROM tabnatural1 NATURAL RIGHT OUTER JOIN tabnatural2); ------------------- | a | b | c | ------------------- | 1 | x | NULL | | 2 | y | z | | 3 | NULL | w | ------------------- **************************************************************** **************************************************************** SELECT * FROM tabnatural1 JOIN tabnatural2 ON tabnatural1.a = tabnatural2.a is equivalent to SELECT * FROM tabnatural1, tabnatural2 WHERE tabnatural1.a = tabnatural2.a ----------------- | b | a | a | c | ----------------- | y | 2 | 2 | z | ----------------- We saw how to express natural joins, and we saw the join … using clause, which is a form of natural join that requires values to match only on specified attributes. When the JOIN clause is used without the OUTER prefix, is the INNER JOIN. So SELECT * FROM tabnatural1 JOIN tabnatural2 USING (a) is equivalent to SELECT * FROM tabnatural1 INNER JOIN tabnatural2 USING (a) Similarly, NATURAL JOIN is equivalent to NATURAL INNER JOIN.