Joins in SQL in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a JOIN in SQL?

A JOIN in SQL is a way to combine rows from two or more tables based on a related column between them.

beginner

What does an INNER JOIN do?

An INNER JOIN returns only the rows where there is a match in both tables based on the join condition.

beginner

Explain LEFT JOIN in SQL.

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result will have NULLs for the right table's columns.

intermediate

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN returns all rows from the left table and matching rows from the right. RIGHT JOIN returns all rows from the right table and matching rows from the left.

intermediate

What is a FULL OUTER JOIN?

A FULL OUTER JOIN returns all rows when there is a match in one of the tables. It combines the results of LEFT JOIN and RIGHT JOIN, showing all rows from both tables with NULLs where there is no match.

Which JOIN returns only matching rows from both tables?

A LEFT JOIN

B INNER JOIN

C RIGHT JOIN

D FULL OUTER JOIN

INNER JOIN returns only rows where the join condition matches in both tables.

What does a LEFT JOIN return?

A Only rows matching in both tables

B All rows from the right table and matching rows from the left

C All rows from the left table and matching rows from the right

D All rows from both tables

LEFT JOIN returns all rows from the left table and matched rows from the right table.

If you want all rows from both tables, which JOIN should you use?

A INNER JOIN

B LEFT JOIN

C RIGHT JOIN

D FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, matching where possible.

Which JOIN can result in NULL values in the result set?

A B and D

B LEFT JOIN

C INNER JOIN

D RIGHT JOIN

LEFT JOIN and RIGHT JOIN can include rows with NULLs where there is no matching row in the other table.

What is the main purpose of using JOINs in SQL?

A To combine rows from two or more tables

B To delete rows from tables

C To create new tables

D To update data in a table

JOINs are used to combine rows from two or more tables based on related columns.

Describe the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Think about which table's rows are always included and where NULLs appear.

Explain a real-life situation where you might use a LEFT JOIN in a database query.

Consider a list of people and their activities where some people might not have done the activity.