Table of contents
1.
Introduction
1.1.
Demo Database table
1.2.
Self Join
1.3.
Inner Join
1.4.
EQUI Join
1.5.
NON-EQUI Join 
2.
Frequently Asked Questions
3.
Key Takeaways 
Last Updated: Mar 27, 2024

Joins-2

Author Jainish Patel
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

This article is the continuation of this article. The article will cover remaining Joins like SELF, EQUI, and NON-EQUI with different examples and queries.

Recommended topic, LIMIT in SQL

Demo Database table

All the queries apply to the following Database Tables.

Consider the Students table having all the student’s data consisting of columns like RollNo, Name, City, Phone, Age.

Consider the other table Courses with details of students enrolling in the different courses consisting of columns like CourseID and RollNo.

Note: Table 1 is considered the Students table, and table 2 is considered the Courses table.

Self Join

As the name implies, a table is joined to itself with SELF JOIN. While most JOINs join two or more tables with each other to present their data together, a self join links the table to itself. This is often accomplished by connecting a table to itself just once within a SQL query, while it is possible to do it numerous times within the same query using subqueries. 

Syntax

SELECT Table1.Column1 , Table2.Column2
FROM table_name as Table1, table_name as Table2
WHERE some_condition;

Let consider an example,

Write a query to print the RollNo and Name from the table Students where the rollno is less than its next rollno. For example, if we have the rollno. 1,2,3,4(1<2, 1<3, 1<4) and (2<3, 2<4) and (3<4). 

SQL Query:  

Select a.RollNo as ARollNO, b.RollNo as BRollNO
FROM Students as a, Students as b
WHERE a.RollNo < b.RollNo;

 

Note: and is Students Table. Self join works on the same table.

Result for the above query:

Inner Join

As long as the condition is satisfied, the INNER JOIN keyword selects all rows from both tables. This keyword will generate a result-set by combining all rows from both tables that meet the requirements, i.e., the common field's value will be the same.

 

Source:StackOverFlow

Syntax

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1 
INNER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

It is also valid to write JOIN instead of INNER JOIN. Both keywords refer to the same thing.

Let consider an example,

From the given two tables named as Students and Courses. We need to join two tables so that the resultant table includes common columns of both the tables and print only Name, and age from Table 1 and CourseID from table 2 where the Rollno must be the same in both tables. 

SQL Query:  

SELECT Students.Name, Students.Age, Courses.CourseID
FROM Students
INNER JOIN Courses
ON Students.RollNo = Courses.RollNo;

Result for the above query:

EQUI Join

EQUI JOIN creates a JOIN between two tables for equality or matching column values. EQUI JOIN also creates a JOIN by using ON and then providing the names of the columns and their corresponding tables to check equality using the equal sign (=).

Syntax

SELECT ColumnName(s)  
FROM Table1, Table2....
WHERE Table1.ColumnName = Table2.ColumnName;

Or 

Syntax

SELECT ColumnName(s)  
FROM Table1  
JOIN Table2
[ON (join_condition)];

Let consider an example,

From the given two tables named as Students and Courses. Write a query to join both tables on the basis of RollNo of Courses table equal to RollNo of Students table and print only RollNo and Name from Table 1 and CourseID from table 2.

SQL Query:  

SELECT Students.RollNo,Students.Name,Courses.CourseID 
FROM Students
JOIN Courses
ON Courses.RollNo = Students.RollNo;

Result for the above query:

NON-EQUI Join 

NON-EQUI JOIN performs a JOIN with conditions using comparison operators other than equal(=), such as >,  <, >= and <=.

Syntax

SELECT *  
FROM Table1, Table2  
WHERE Table1.column [> |  < |  >= | <= ]Table1.column;

Let consider an example,

From the given two tables named as Students and Courses. We need to join two tables where RollNo from Table1 is less than CourseID of Table2 and print RollNo and Name from table 1 and CourseID from table 2.

SQL Query:  

SELECT Students.RollNo, Students.Name, Courses.CourseID
FROM Students, Courses
WHERE Students.RollNo <= Courses.CourseID;

 

Result for the above query:

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

Frequently Asked Questions

1. Self Join is an Inner Join or Outer Join?

A self-join can be a cross join, an inner join, or an outer join. A table is linked by a column containing duplicate data in different rows.

 

2. What are the basic types of joins?

There are two basic types of joins in SQL : 

Joins using Operators -> EQUI Join, Non-EQUI Join 

Joins using Concept-> Inner Join, Outer Join, Cross Join, Self Join.

 

3. Can you join the table by itself? If Yes, how? If not, Why?

  • Using Self-Joins, A table can be joined to itself in a self-join.
  • Use a self-join when you want to create a result set that joins records in a table with other records in the same table.
  • To list a table twice in the same query, you must provide a table alias for at least one instance of the table name.

Key Takeaways 

This article discusses how to use the different JOINS using various examples and writing SQL queries. 

Also Read - Cardinality In DBMS

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Furthermore, to get hang on and practice different types of questions on SQL queries, you can visit Database Management Systems FREE Guided path. If you want to learn DBMS from an industry expert, check out a fantastic course on DBMS

Live masterclass