Multiple Joins in SQL
Structured Query Language or SQL is a standard database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. In this article, we will be using the Microsoft SQL Server.
Multiple Joins :
Here we are going to implement the concept of multiple joins. Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables. For this article we will first create a database Beginner and then create three tables in it and then run our queries on those tables.
1. Creating Database :
CREATE Beginner;
Output –
Query ok, 1 row affected
2. To use this database :
USE Beginner;
Output –
Database changed
3. Adding Tables to the Database :
create table students(id int, name varchar(50),branch varchar(50)); create table marks(id int, marks int); create table attendance(id int, attendance int);
Output –
Query ok, 0 row affected
Query ok, 0 row affected
Query ok, 0 row affected
4. Inserting Data into Tables:
Students table –
--students insert into students values(1,'anurag','cse'); insert into students values(2,'harsh','ece'); insert into students values(3,'sumit','ece'); insert into students values(4,'kae','cse');
Output –
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
5. Marks Table :
--marks insert into marks values(1,95); insert into marks values(2,85); insert into marks values(3,80); insert into marks values(4,65);
Output –
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
6. Attendance table :
--attendance insert into attendance values(1,75); insert into attendance values(2,65); insert into attendance values(3,80); insert into attendance values(4,80);
Output –
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
Query ok, 1 row affected
7. View data inside the tables :
select *from students;
Output –
Students Table –
id | name | branch |
1 | anurag | cse |
2 | harsh | ece |
3 | sumit | ece |
4 | kae | cse |
select *from marks;
Output –
Marks Table-
id | marks |
1 | 95 |
2 | 85 |
3 | 80 |
4 | 65 |
select *from attendance;
Output –
Attendance table-
id | attendance |
1 | 75 |
2 | 65 |
3 | 80 |
4 | 87 |
Screenshot of Final Output –
8. Performing Multiple Joins :
Now we will perform multiple joins on our tables. First we will inner join the students and the marks tables and then we will join the resulting table with the attendance table only for those students which have attendance greater than or equal to 75.
Syntax –
JOIN table1.column_name=table2.column_name JOIN table2.column_name=table3.column_name
Example query :
select s.id, name, marks, attendance from students as s inner join marks as m on s.id=m.id inner join attendance as a on m.id=a.id where a.attendance>=75;
Output –
id | name | marks | attendance |
1 | anurag | 95 | 75 |
3 | sumit | 80 | 80 |
4 | kae | 65 | 87 |
Screenshot of Final Output –
Contact Us