SQL - Multiple Tables Flashcards
_______ will combine rows from different tables if the join condition is true.
JOIN
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
_______ will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
LEFT JOIN
SELECT *
FROM table1
LEFT JOIN table2
ON table1.c2 = table2.c2;
_______ is a column that serves a unique identifier for the rows in the table.
Primary key
_______ is a column that contains the primary key to another table.
Foreign key
_______ lets us combine all rows of one table with all rows of another table.
CROSS JOIN
SELECT shirts.shirt_color,
pants.pants_color
FROM shirts
CROSS JOIN pants;
_______stacks one dataset on top of another.
UNION
SELECT *
FROM table1
UNION
SELECT *
FROM table2;
_______ allows us to define one or more temporary tables that can be used in the final query.
WITH
WITH previous_query AS (
SELECT customer_id,
COUNT(subscription_id) AS ‘subscriptions’
FROM orders
GROUP BY customer_id
)
SELECT customers.customer_name,
previous_query.subscriptions
FROM previous_query
JOIN customers
ON previous_query.customer_id = customers.customer_id;
What is the difference between an INNER JOIN and a LEFT JOIN?
LEFT JOIN combines rows from two or more tables, but unlike INNER JOIN, it does not require the join condition to be met.
Why is a CROSS JOIN not so useful?
It combines every row in one table with every row in another table.
You have two tables authors and books. Each book belongs to an author and references that author through a foreign key. If the primary key of the authors table is id, what would be the most sensical name for a foreign key in the books table that references the id column in authors?
author_id
What is the best definition of a primary key?
A unique identifier for each row or record in a given table.
In a LEFT JOIN, if a join condition is not met, what will it use to fill columns on the right table?
NULL values
You have two tables teachers and students. Each student belongs to a teacher. Complete the query to join the tables on the teacher id.
SELECT *
FROM students
JOIN teachers
ON __________________;
students.teacher_id = teachers.id
UNION allows us to stack one dataset on top of another. T/F
T
Which keyword would you use to alias recipes.name and chefs.name in the following query’?
SELECT recipes.name __ ‘Recipe’,
chefs.name __ ‘Chef’
FROM recipes
JOIN chefs
ON recipes.chef_id = chefs.id;
AS
