Inner Join SQL

JOIN in SQL are used to combine rows from two or more tables
based on a common field between them, thus returning data from different

A JOIN occurs when two or more tables are joined in one SQL

For example, INNER JOIN, Returns all rows when there is at least
one match in both tables.

LEFT JOIN Returns all rows from the left table, and matching rows
from the right table.

RIGHT JOIN Returns all rows from the right table, and matching
rows from the left table.

OUTER JOIN, Returns all the rows of the two tables, the left and
the right. Also called FULL OUTER JOIN.

However, this article focuses primarily on the INNER JOIN


The INNER JOIN clause searches for matches between rows in the product
and category tables.

If a row or record in the products table has the same value in the column
as a row in the categories table, the query combines the values ​​of the
columns specified in the SELECT list into a new row and includes
that new row in the result set.

It is the most common type of JOIN.

SELECT columnName(s) FROM table1 INNER JOIN table2 ON

In mathematical terms, it is nothing more than the union of two sets that
have elements in common, so that if we make a diagram, it would look like

The example to be used will be in the WideWorldImporters Database,
with the Purchasing.Suppliers and Purchasing.SupplierTransactions tables,
it will seek to obtain the name of the supplier and the amount of the
transaction, where the IDs in both tables match.

SELECT Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.PaymentMethodID FROM
Purchasing.Suppliers JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID =

And the following result is obtained:

It is also possible to obtain a specific result for each supplier, giving
the identifier number, for example:

SELECT Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.TransactionDate FROM
Purchasing.Suppliers INNER JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID = 4

Where you can see that you want to obtain the amounts of the transaction
and the corresponding dates, from the supplier identified as 4 (Fabrikam,

INNER JOIN on three Tables

It is also possible to get data from three tables, using the INNER JOIN
clause, twice, as follows:

The following statement uses two INNER JOIN clauses to query data from
all three tables:

  • Purchasing.Suppliers
  • Purchasing.SupplierTransactions
  • Purchasing.SupplierCategories

SELECT TOP(20) Purchasing.Suppliers.SupplierName,
FROM Purchasing.Suppliers
INNER JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID =
INNER JOIN Purchasing.SupplierCategories ON
Purchasing.Suppliers.SupplierCategoryID = 2

Obtaining the following result:

Leave a Reply

Your email address will not be published.