JOINs

AS

AS enables users to rename columns or table selections with an alias.

SELECT payment_id AS "Payment ID",
       customer_id AS "Customer ID",
       staff_id AS "Staff ID"
  FROM payment;

AS can also be applied to aggregate columns.

SELECT customer_id AS "Customer ID",
       SUM(amount) AS "Total Spent"
  FROM payment
 GROUP BY customer_id
 ORDER BY "Total Spent" DESC

JOINs

For future reference, the relational schema used in this course is included below.

JOINs allow users to relate data in multiple tables together. There are several kinds of joins, including INNER JOIN, OUTER JOIN, and self-join.

JOINs typically operate by connecting the primary key of one table to the foreign key of another table. Typical syntax follows, where A and B are names of tables, pka is the primary key of table A, and fka is the foreign key of table B that connects it to A.

SELECT A.pka,
       A.c1,
       B.pkb,
       B.c2
  FROM A
 INNER JOIN B
    ON A.pka = B.fka;

INNER JOINs work by checking the corresponding columns of both tables for matching values. If it finds one, then it connects the corresponding rows of those two tables into a single table.

SELECT payment.customer_id,
       payment_id,
       first_name,
       last_name,
       amount
  FROM payment
  JOIN customer
    ON payment.customer_id = customer.customer_id;

Some examples.

SELECT payment_id,
       amount,
       first_name "Staff First Name",
       last_name "Staff Last Name"
  FROM payment
 INNER JOIN staff
    ON payment.staff_id = staff.staff_id;

INNER JOINs are actually the default JOIN, so most SQL engines will allow you to specify JOIN only and still get the INNER JOIN functionality.

SELECT title,
       COUNT(title) AS "Total Copies",
       store_id
  FROM film
  JOIN inventory
    ON film.film_id = inventory.film_id
 GROUP BY title, store_id
 ORDER BY title, store_id;

As shown below, table names are frequently aliased, and the AS statement can be omitted for brevity.

SELECT store_id,
       COUNT(title) "Total Movies"
  FROM film f
  JOIN inventory i
    ON f.film_id = i.film_id
 GROUP BY store_id;

Types of JOINs

The various types of joins come into play when the tables being JOINed have missing values. The various types of JOINs allow the user to rectify this situation in various ways.

The following image is the most concise means of describing the various types of JOINs. This image was generated by this prolific Reddit user, originally posted here, and currently hosted here.

When performing JOINs, the left table is the table that follows the FROM statement. The right table is the table that follows ON.

Examples of Join Types

The following example is presented in the Udemy course and reproduced here. Two columns each of two tables, A and B, are represented.

A.ID A.Name B.ID B.Name
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

In the following examples, LEFT JOINs can be turned into RIGHT JOINs by either replacing the relevant word in the queries. It is also possible to accomplish the same thing by swapping which tables appears after JOIN and ON.

INNER JOIN

SELECT *
  FROM A
  JOIN B
    ON A.Name = B.Name
A.ID A.Name B.ID B.Name
1 Pirate 2 Pirate
3 Ninja 4 Ninja

LEFT OUTER JOIN

SELECT *
  FROM A
  LEFT JOIN B
    ON A.Name = B.Name
A.ID A.Name B.ID B.Name
1 Pirate 2 Pirate
2 Monkey
3 Ninja 4 Ninja
4 Spaghetti

FULL OUTER JOIN

SELECT *
  FROM A
  FULL OUTER JOIN B
    ON A.Name = B.Name
A.ID A.Name B.ID B.Name
1 Pirate 2 Pirate
2 Monkey
3 Ninja 4 Ninja
4 Spaghetti
1 Rutabaga
3 Darth Vader

LEFT OUTER JOIN with WHERE

SELECT *
  FROM A
  LEFT JOIN B
    ON A.Name = B.Name
 WHERE B.ID IS null
A.ID A.Name B.ID B.Name
2 Monkey
4 Spaghetti

FULL OUTER JOIN with WHERE

SELECT *
  FROM A
  FULL OUTER JOIN B
    ON A.Name = B.Name
 WHERE A.ID IS null
    OR B.ID IS null
A.ID A.Name B.ID B.Name
2 Monkey
4 Spaghetti
1 Rutabaga
3 Darth Vader

UNION

UNION combines the results of multiple SELECT statements into a single set of results. Two rules must be followed when using UNION:

  • All queries must return the same number of columns,
  • Corresponding columns in the queries must have compatible data types.

The syntax follows.

SELECT column_1,
       column_2
  FROM table_A
 UNION
SELECT column_1,
       column_2
  FROM table_B

Reasons to use UNION include:

  • UNION removes all duplicate rows unless UNION ALL is used. Duplicate rows are defined as having the same data across all columns.
  • UNION is frequently used to combine data from similar tables that are not perfectly normalized, meaning, the information has not been combined into a single table yet.

Content for this note is taken from information in “The Complete SQL Bootcamp” course on Udemy. I highly recommend it.