ORIE 3120
Lecture 5: SQL #4 [INNER JOIN]
1
2INNER JOIN
Joining Tables in Queries
• Having the capability to select data from
multiple tables is one of SQL’s most powerful
features.
• The most practical queries are those whose
data is acquired from multiple tables within
the database.
Joins
• A join combines two or more tables to retrieve data from
multiple tables.
• They often join a child’s foreign key on the field it references
in the parent.
• We’ll cover a few types of joins
– Inner joins
– Left joins
– Right joins
– Full Outer joins
Inner Join
• The inner join joins two tables with a common column.
• Let’s look at the Products and Suppliers
tables from our previous example
• I want a list of product names with the
name of the company that supplies
each product
Inner Join
• As you can see, the data is in two tables.
• ProductName is in the Products table
• CompanyName is in the Suppliers table
• We can bring them together using an
inner join
Inner Join Syntax
SELECT Suppliers.SupplierID,
Products.ProductName,
Suppliers.CompanyName
FROM Products, Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID;
Example: INNER JOIN
SELECT Suppliers.SupplierID,
Products.ProductName,
Suppliers.CompanyName
FROM Products,Suppliers
WHERE Products.SupplierID =
Suppliers.SupplierID;Suppliers
Products
Let’s practice (Q1)
SELECT T1.id, T1.a, T2.b
FROM T1
INNER JOIN T2
ON T1.id = T2.id
T1 T2
How many records are
returned?
(a) 3
(b) 4
(c) 5
(d) 6
(e) 7
Let’s practice (Q2)
SELECT T1.id, T1.a, T2.b
FROM T1
INNER JOIN T2
ON T1.a > T2.b
T1 T2
How many records are
returned?
(a) 4
(b) 6
(c) 8
(d) 10
(e) 12
Qualify columns to prevent ambiguity
• Each column in this SELECT clause is preceded by the associated
table name
• This is called qualifying the columns in a query.
• Qualifying the columns is only needed for columns that exist in
more than one table referenced by a query.
Did we need to qualify this column?
SELECT Products.ProductName,
Suppliers.CompanyName
FROM Products, Suppliers
WHERE Products.SupplierID =
Suppliers.SupplierID
(a) Yes
(b) No
Did we need to qualify this column?
SELECT Products.ProductName,
Suppliers.CompanyName
FROM Products, Suppliers
WHERE Products.SupplierID =
Suppliers.SupplierID
(a) Yes
(b) No
Did we need to qualify these columns?
SELECT Products.ProductName,
Suppliers.CompanyName
FROM Products, Suppliers
WHERE Products.SupplierID =
Suppliers.SupplierID
(a) Yes
(b) No
Alternative Inner Join Syntax
SELECT Products.ProductName,
Suppliers.CompanyName
FROM Products
INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
You can choose which syntax to use
• You can use either syntax
• You should understand both