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:
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
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
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