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: a and b 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.