The students needed yet another example of LEFT
JOIN
, RIGHT JOIN
, and FULL JOIN
syntax (by combining a left and right join with the UNION set
operator). To that end, I put this set of examples together.
The example also shows how to order the result set from a derived
table with the UNION
operator. It uses the
WITH
clause to build a Common Table Expression
(CTE), which allows the query to order the UNION
set
operator’s product based on the left and right join queries. It
uses a CASE
statement to order the result sets. The
left_table
is the parent table and the
right_table
is the child table in the relationship,
which means the right_table
holds a
left_id
foreign key column that lets you connect
matching rows in the left_table
.
You build the little model with the following …
[Read more]