When applying a join condition, we have to specify the column from both the tables on which the join should base on. This column is typically a foreign key of a secondary table associated with the primary table. We have to use FROM clause in the SQL query to define a join. SQL Server also provides WHERE and HAVING clauses that we can use as search conditions to control the rows that have to be selected from the tables.
The syntax to define a join in SQL query is,
FROM first_table join_type second_table [ON (join_condition)]
first_table is the name of the first table.
second_table is the name of the second table.
join_type is the type of join.
To gain in-depth knowledge with practical experience in SQL server, then explore SQL server Training!
The type of join indicates how rows from one table should be combined with the rows of another table. It defines the way that the two tables are related. These are the various kinds of joins that the SQL Server provides.
We will describe in detail all the joins in the later section of this post.
Let us take a retail scenario where all the customers’ details are stored in a ‘Customers’ table and the associated sales details are stored in a ‘Sales’ table.
Create a table for customers using the below query.
CREATE table customers(
ID int PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Age int NOT NULL,
Gender VARCHAR(10) NOT NULL,
Email VARCHAR(100) NOT NULL
);
Enter data into the table using the INSERT query.
INSERT into customers values(201, 'John Smith', 34, 'Male', 'john@gmail.com')
Use the same INSERT query with different values to insert the rest of the rows to the customers’ table.To view the data that you just entered into the customers’ table, execute the below query.
SELECT * from customers:
You will get the below table as a result.
Create a sales table with the below query.
CREATE table sales(
OrderID int PRIMARY KEY,
CustomerID int NOT NULL,
Category VARCHAR(100) NOT NULL,
OrderedDate Date NOT NULL,
Amount int NOT NULL
);
Enter sales details into the table using the INSERT query.
INSERT into sales values(1241, 204, 'Electronics', '02/08/2020');
Use the same INSERT query with different values to insert the rest of the rows to the sales table. To view the data that you just entered into the sales table, execute the below query.
The inner join is the most used join by developers. It is also known as EQUI join.It combines all the rows of two tables based on a common predicate. Here is the Venn diagram that depicts the inner join between two tables.
Let us apply an inner join on our example tables. Here is the SQL query for it.
SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
FROM customers
INNER JOIN sales
ON customers.ID = sales.CustomerID;
Here is the result of the above query.
The left outer join combines all the rows of the left table and only the rows that meet the join predicate of the right table. Here is the Venn diagram that depicts the left outer join between two tables.
Here is the SQL query for the left outer join on the customers and sales tables.
SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category,sales.OrderedDate
From customers
LEFT OUTER JOIN sales
ON customers.ID = sales.CustomerID;
Here is the result of the above query.
The right outer join combines all the rows of the right table and only the rows that meet the join predicate of the left table. Here is the Venn diagram that depicts the right outer join between two tables.
Here is the SQL query for the right outer join on the customers and sales tables.
SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
From customers
RIGHT OUTER JOIN sales
ON customers.ID = sales.CustomerID;
Here is the result of the above query.
It returns the values of all the rows of the left table and the right table.It places null where the join predicate is not met. Below is the Venn diagram that depicts the full outer join between two tables.
Execute the below SQL query to apply full outer join on the customers and sales tables.
SELECT customers.ID,customers.FullName, sales.OrderID, sales.Category,sales.OrderedDate
From customers
FULL OUTER JOIN sales
ON customers.ID = sales.CustomerID;
Here is the result of the above query.
A self join allows a table to join with itself, which has a unary relationship. Let’s apply a self join on the customers table. Here is the Venn diagram for self-join.
Execute the below query to see how it works.
SELECT A.ID, A.FullName AS CustomerName1, B.FullName AS CustomerName2, A.Gender
FROM Customer A, Customer B
WHERE A.Gender = B.Gender;
The result will be as follows
The cross join is used to create a combination of every row from two tables. It returns the Cartesian product of rows from both tables. It does not establish a relationship between the two tables. Below is the Venn diagram that depicts the cross join between two tables.
Execute the below SQL query to apply a cross join on customers and sales tables.
SELECT customers.ID, customers.FullName, sales.OrderID, sales.Category, sales.OrderedDate
From customers
CROSS JOIN sales;
Here is the partial set of the result set.
SQL Server provides various types of joins that the developers can use. All you have to do is select the one that fits your needs and start implementing it. Create some more tables and try applying joins on two or more tables. If you do not have a SQL Server installed on your system,you can use an online SQL editor to try out the joins.
Related Articles:
1. Isolation Levels in SQL Server
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.
Batch starts on 23rd Mar 2024 |
|
||
Batch starts on 27th Mar 2024 |
|
||
Batch starts on 31st Mar 2024 |
|