SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Join Fundamentals

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

A join condition defines the way two tables are related in a query by:

• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Joins are expressed logically using the following Transact-SQL syntax:

• INNER JOIN
• LEFT [ OUTER ] JOIN
• RIGHT [ OUTER ] JOIN
• FULL [ OUTER ] JOIN
• CROSS JOIN

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Different Types of SQL Joins

Here are the different types of JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

SQL Server INNER JOIN

Inner Join clause in SQL Server creates a new table (not physical) by combining rows that have matching values in two or more tables. This join is based on a logical relationship (or a common field) between the tables and is used to retrieve data that appears in both tables.

Below is the basic syntax of Inner Join.

SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.ColName = TABLE2.ColName

Inner Join syntax basically compares rows of Table1 with Table2 to check if anything matches based on the condition provided in the ON clause. When the Join condition is met, it returns matched rows in both tables with the selected columns in the SELECT clause.
In this example, we are going to create two tables Product and Category table, and apply inner join to them.

CREATE DATABASE kodingnotes;
use  kodingnotes;
CREATE TABLE category(
id INT IDENTITY PRIMARY KEY NOT NULL,
category_name VARCHAR(200)
);

CREATE TABLE products(
id INT IDENTITY PRIMARY KEY NOT NULL,
product_name VARCHAR(200), 
product_price DECIMAL(10,2),
category_id INT,
FOREIGN KEY (category_id) REFERENCES  category(id)
);

--INSERTING MULTIPLE ROW AT A TIME
--HERE WE ARE INSETING RECORDS IN CATEGORY TABLE
INSERT INTO category(category_name) VALUES('ELECTRIC BIKE'),('CYCLE'),('MOTERCYCLE'),('GROCERY')

--INSERTING MULTIPLE ROW AT A TIME
--HERE WE ARE INSETING RECORDS IN PRODUCTS TABLE
INSERT INTO products(product_name, product_price, category_id)
VALUES ('Trik XM+ Lowstep 2018',5000,1), ('Hero Vector 91',9000,2),
('Honda Dio',80000,3),('Mint',200,4)

INNER JOIN STATEMENT

SELECT *  FROM products 
INNER JOIN category ON products.category_id = category.id --Here we are comparing both table common records

--Complete inner join statement with both table column name
SELECT products.id, products.product_name, products.product_price, category.category_name  FROM products 
INNER JOIN category ON products.category_id = category.id

Final Result of Inner Join  

SQL Server LEFT (OUTER) JOIN

SQL left outer join is also known as SQL left join. Suppose, we want to join two tables: A and B. SQL left outer join returns all rows in the left table (A) and all the matching rows found in the right table (B). It means the result of the SQL left join always contains the rows in the left table.

In a SQL Left Outer Join, we get the following rows in our output.

• It gives the output of the matching row between both the tables
• If no records match from the left table, it also shows those records with NULL values

INSERT INTO products(product_name, product_price)
VALUES ('Trik XM+ Lowstep 2018',5000), ('Hero Vector 91',9000),
('Honda Dio',80000),('Mint',200)

--INNER JOIN 
SELECT *  FROM products 
INNER JOIN category ON products.category_id = category.id --Here we are comparing both table common records
--LEFT OUTER JOIN
SELECT * FROM products LEFT OUTER JOIN category ON products.category_id = category.id

Here LOJ (LEFT OUTER JOIN) Result of the left outer join with Inner Join as you can see result of both the joins are different