SQL joins

When I do interviews, one of the questions that we ask is about SQL and how to get data out of a couple of tables. Depending on the level of the candidate we can extend this question format to ask more details. Without going in to the specifics of our interview question, I thought I would try and have a quick explanation of the various SQL joins.

Setup

Through out this blog, we will have two tables. They will be related to a product management system. In this, we will have two tables, with the following structure and data:

Product:

Supplier:

Inner join

An inner join returns rows that have matching values in both tables

select * from Supplier s
inner join Product p on p.SupplierId = s.Id

This query is useful to get all of the products and suppliers with a known supplier.

Left join

A left join will return all rows from the left table, and matched rows in the right table

select * from Supplier s
left join Product p on p.SupplierId = s.Id

This query is useful to get all of the suppliers, and the products that they supply, even if they do not supply anything.

Right join

A right join will return all rows from the right table, and matched rows in the left table

select * from Supplier s
right join Product p on p.SupplierId = s.Id

This query is useful to get all of the product, and their supplier, even if we do not have the details of the supplier.

Full outer join

A full outer join returns all rows where there is a match in either the left or right table

select * from Supplier s
full outer join Product p on p.SupplierId = s.Id

This query is useful to get all of the products and suppliers, even if the suppliers does not supply anything, and we don’t know the supplier of a product.

For more information, check out:

• W3 Schools
• Script to create tables in an existing database

create table Supplier(
	Id int PRIMARY KEY 
	, Name varchar(50)	
)

create table Product (
	Id int PRIMARY KEY
	, [Description] varchar(50) 
	, SupplierId int null FOREIGN KEY REFERENCES Supplier(Id)
)

insert into Supplier (Id,Name)
values 
	(1,'SupplierA'),
	(2,'SupplierB'),
	(3,'SupplierC')

insert into Product (Id,[Description],SupplierId)
values 
	(1,'ProductA',1),
	(2,'ProductB',3),
	(3,'ProductC',null),
	(4,'ProductD',1)

• sql
• interviews