All the information collected through applications will be stored in tables in databases. Every application might have more than one table that holds data. This kind of data among various tables have relations between them. So, when users need to access complete data, they have to combine it from various tables. This is where joins are helpful. SQL Server provides several joins. In this post, you can get to know about all the joins of SQL Server. We will give you a detailed understanding of the joins with examples. Let us get started.
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', 'j[email protected]')
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.
Explore Sql server sample resume ! Download & Edit, Get Noticed by Top Employers!
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:
2. Data Modelling in SQL Server
3. Isolation Levels in SQL Server
4. Exception Handling in SQL Server
Batch starts on 3rd Jun 2023, Weekend batch
Batch starts on 7th Jun 2023, Weekday batch
Batch starts on 11th Jun 2023, Weekend batch