Joining on primary key to foreign key in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a primary key in a database table?

A primary key is a unique identifier for each row in a table. It ensures that no two rows have the same value in that column or set of columns.

beginner

What is a foreign key in a database table?

A foreign key is a column or set of columns in one table that refers to the primary key in another table. It creates a link between the two tables.

beginner

Why do we join tables on primary key to foreign key?

We join tables on primary key to foreign key to combine related data from two tables, like matching a customer with their orders.

beginner

Write a simple SQL query to join two tables on primary key to foreign key.

SELECT * FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

intermediate

What happens if you join tables on columns that are not primary key and foreign key?

The join might return incorrect or duplicate data because the link between tables is not guaranteed to be unique or valid.

What does a foreign key in a table represent?

A A link to the primary key in another table

B A unique identifier for each row in the same table

C A column that stores only numbers

D A column that cannot have NULL values

A foreign key links to the primary key in another table to connect related data.

Which SQL clause is used to combine rows from two tables based on a related column?

A WHERE

B GROUP BY

C JOIN

D ORDER BY

JOIN is used to combine rows from two tables based on a related column.

In the query: SELECT * FROM A JOIN B ON A.id = B.a_id; which is likely the primary key?

A B.a_id

B Neither is a primary key

C Both are primary keys

D A.id

A.id is likely the primary key, and B.a_id is the foreign key referencing it.

What is the main benefit of joining tables on primary key to foreign key?

A Faster data entry

B Ensures data integrity and correct matching

C Allows duplicate rows

D Removes all NULL values

Joining on primary key to foreign key ensures data integrity and correct matching of related rows.

If a foreign key value does not match any primary key, what happens in an INNER JOIN?

A The row is excluded from the result

B The row is duplicated

C The query fails with an error

D The row is included with NULLs

In an INNER JOIN, rows without matching keys are excluded from the result.

Explain in your own words why joining tables on primary key to foreign key is important.

Think about how two tables relate to each other in real life, like customers and their orders.

Describe how you would write a SQL query to join two tables using a primary key and foreign key.

Remember the format: SELECT * FROM Table1 JOIN Table2 ON Table1.key = Table2.key