SQL Server JOINS - INNER JOIN

INNER JOIN

This JOIN returns all records from multiple tables that satisfy the specified join condition. It is the simple and most popular form of join and assumes as a default join. If we omit the INNER keyword with the JOIN query, we will get the same output.

The following visual representation explains how INNER JOIN returns the matching records from table1 and table2:

The SQL INNER JOIN joins two tables based on a common column and selects rows that have matching values in these columns.

SQL INNER JOIN Syntax

The syntax of the SQL INNER JOIN statement is:

SELECT columns_from_both_tables
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2

Here,

• table1 and table2 are the two tables that are to be joined

• column1 is the column in table1 that is related to column2 in table2

INNER JOIN excludes all the rows that are not common between two tables.

Note: We can also use JOIN instead of INNER JOIN. Basically, these two clauses are the same.

Example 2: SQL INNER JOIN

Let's look at another example,

-- join Categories and Products tables with their matching fields cat_id
SELECT Categories.cat_name, Products.prod_title 
FROM Categories 
INNER JOIN Products 
ON Categories.cat_id = Products.cat_id;

Here, the SQL command selects common rows between Categories and Products tables with the matching field cat_id.

The result set has the cat_name column from Categories and the prod_title column from Products.

INNER JOIN With WHERE Clause

Here's an example of INNER JOIN with the WHERE clause:

-- join Customers and Orders table with matching fields customer_id and customer
SELECT Customers.customer_id, Customers.first_name, Orders.amount 
FROM Customers 
INNER JOIN Orders 
ON Customers.customer_id = Orders.customer WHERE Orders.amount >= 500;  

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

SQL INNER JOIN With AS Alias

We can use AS aliases inside INNER JOIN to make our query short and clean. For example,

-- use alias C for Categories table
-- use alias P for Products table 
SELECT C.cat_name, P.prod_title 
FROM Categories AS C 
INNER JOIN Products AS P ON C.cat_id= P.cat_id;  

Here, the SQL command performs an inner join on the Categories and Products tables while assigning the aliases C and P to them, respectively.

SQL INNER JOIN With Three Tables

We can also join more than two tables using INNER JOIN. For example,

-- join three tables:

Customers, Orders, and ShippingsSELECT C.customer_id, C.first_name, O.amount, S.status 
FROM Customers AS C 
INNER JOIN Orders AS O ON C.customer_id = O.customer INNER JOIN Shippings AS S ON C.customer_id = S.customer;  

Here, the SQL command

• joins Customers and Orders tables based on customer_id (from the Customers table) and customer (from the Orders table)

• and joins Customers and Shippingss tables based on customer_id (from the Customers table) and customer (from the Shippings table)

The command returns those rows where there is a match between column values in both join conditions.

Note:

For this command to run, there must be a customer_id column in each individual table. The column names can be different as long as they have common data.

---