Joins

Since solving any reasonable SQL problem requires a combination of all the topics covered here, hence it becomes difficult to seggregate problems based on one topic alone. So for SQL we are creating a dedicated Problems section. Theoritical and Basic questions will still be under their dedicated sections.

Joins are best explained using Venn diagrams

TSQL JOIN Types, Reference

Remember that in case of multiple joins each single join produces a single derived table that is then joined to the next table and so on.

Questions

Address of People

Reference - Leetcode

Table: Person

| Column Name | Type    |
|-------------|---------|
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |

PersonId is the primary key column for this table.

Table: Address


| Column Name | Type    |
|-------------|---------|
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |

AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless of if there is an address for each of those people:

FirstName, LastName, City, State

Answer

select a.FirstName, a.LastName, b.City, b.State
from Person a
left join Address b
on a.PersonId = b.PersonID