Learn SQL: Course OverviewCourse Overview

Learn SQL: Multiple Tables

Expand your SQL skills by creating and manipulating databases with multiple related tables

Start

Key Concepts

Review core concepts you need to learn to master this subject

JOINing SQL records

SQL LEFT JOIN

The primary key column in a SQL table

SQL foreign key

SQL CROSS JOIN

The SQL UNION Clause

SQL WITH clause

JOINing SQL records

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

The JOIN clause allows for the return of results from more than one table by joining them with other results based on common properties described by ON. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON. In this example, results from the books table are joined with results from the authors table in order to display an author for each book through the shared value of author_id.

SQL `LEFT JOIN`

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

In SQL, the LEFT JOIN clause is used to combine data from tables on a common property that the user specifies using an ON clause. LEFT JOIN takes all the data from the left table but only includes data from the right table if the data matches a value in the other table based on the ON clause. If there is no match, the corresponding right table value will be set to NULL in the result.

The primary key column in a SQL table

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

A primary key column in a SQL table is used to uniquely identify each record in that table. A primary key cannot be NULL. In the example, customer_id is the primary key. The same value cannot re-occur in a primary key column. Primary keys are often used in JOIN operations.

SQL foreign key

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

A foreign key is a reference in one table's records to the primary key from another table. To maintain multiple records for a specific row, the use of foreign key plays a vital role. For instance, to track all the orders of a specific customer, the table order (illustrated at the bottom of the image) can contain a foreign key.

SQL `CROSS JOIN`

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

The SQL CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all combinations of two sets, either from two full tables or from a smaller set of values.

The SQL UNION Clause

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

The SQL UNION clause is used to combine results that appear from multiple SELECT statements and filter duplicates.

For example, given a first_names table with rows of name "Cassidy", "James" and "Hermione", and a last_names table with rows of name "James", "Granger" and "Cassidy", the result of this query would produce four names: "Cassidy", "James", "Hermione" and "Granger".

SQL `WITH` clause

SELECT * 
FROM books
JOIN authors
  ON books.author_id = authors.id;

WITH clause lets you store the result of a query in a temporary table(temporary_movies) using an alias. You can also define multiple temporary tables using a comma and with one instance of the WITH keyword.

Multiple Tables

Lesson 1 of 1

1
In order to efficiently store data, we often spread related information across multiple tables. For instance, imagine that we're running a magazine company where users can have different types of ...
2
Let's return to our magazine company. Suppose we have the three tables described in the previous exercise – shown in the browser on the right (we are going to try something new!): - [...] - [.....
3
Combining tables manually is time-consuming. Luckily, SQL gives us an easy sequence for this: it's called a [...] . If we want to combine [...] and [...] , we would type: [...] Let's break d...
4
Let's revisit how we joined [...] and [...] . For every possible value of [...] in [...] , there was a corresponding row of [...] with the same [...] . What if that wasn't true? For ins...
5
What if we want to combine two tables and keep some of the un-matched rows? SQL lets us do this through a command called [...] . A left join will keep all rows from the first table, regardless ...
6
Let's return to our example of the magazine subscriptions. Recall that we had three tables: [...] , [...] , and [...] . Each of these tables has a column that uniquely identifies each row of th...
7
So far, we've focused on matching rows that have some information in common. Sometimes, we just want to combine all rows of one table with all rows of another table. For instance, if we had a tab...
8
Sometimes we just want to stack one dataset on top of the other. Well, the [...] operator allows us to do that. Suppose we have two tables and they have the same columns. [...] : | pokem...
9
Often times, we want to combine two tables, but one of the tables is the result of another calculation. Let's return to our magazine order example. Our marketing department might want to know a b...
10
In this lesson, we learned about relationships between tables in relational databases and how to query information from multiple tables using SQL. Let's summarize what we've learned so far: - [....