The following join operations (syntax) are not supported in this release:
• CROSS JOIN: Functionally similar to the implicit joins.
SELECT * FROM t1 CROSS JOIN t2
• NATURAL JOIN: Also referred to as natural, equi-join selects rows from the tables that have same value for columns with the same name.
SELECT * FROM t1 NATURAL JOIN t2
• Condition JOIN: Uses the keyword ON to specify the JOIN condition between tables. The scope of fields referred in the ON condition is restricted.
SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c3
WHERE search-condition
• Column Name JOIN: Specifies a more restricted form of NATURAL join. NATURAL joins use all columns with the same names to manage the matching process. The column name JOIN specifies which column values should be matched.
SELECT * FROM t1 JOIN t2 USING (c1, c2)
• RIGHT OUTER JOIN: Preserves unmatched rows from the right table.
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c3
WHERE search-condition
• FULL OUTER JOIN: Preserves unmatched rows from both the left and right tables.
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c3
WHERE search-condition
• UNION JOIN: Creates a new virtual table with the union of all columns from the source tables. The UNION join has no provision for column matching.
• INTERSECT Operator: Allows you to check which rows exist in both tables. INTERSECT returns all rows that exist in the intersection of two tables.
The following query selects all discontinued albums that have been re-released.
SELECT * FROM music_titles
INTERSECT
SELECT * FROM discontinued_albums
• EXCEPT Operator: Allows you to check which rows exist in one table and not the other. EXCEPT returns all rows that only exist in the first table.
The following query helps get all albums in current release that have never been discontinued.
