Joins in SQL

Joins in SQL

A Join is a query that combines rows from two or more tables.
In a join query, more than one table are listed in FROM clause. The function of combining data from multiple tables is called joining.
SQL can obtain data from several related tables by performing either a physical or virtual join on the tables.
The WHERE Clause is most often used to perform the JOIN function with two or more tables have columns.

Types of SQL Joins :

There are different kinds of SQL joins :

  • Equi join
    • Inner Join
    • Outer Join
      • Left Outer Join
      • Right Outer Join
  • Self join
  • Non equi join
  • Natural join

Joins are used when we have to select data from two or more tables. Joints are used to extract data from two (or more) tables, when we need a relationship between certain columns in these tables.

The SQL join condition is always used in the WHERE clause of SELECT, UPDATE and DELETE statements.

Equi join :

Equi join is a simple SQL join condition that uses equals sign as a comparison operator.

Syntax :
SELECT col1,col2,col3
FROM table1,table2
WHERE table1.col1 = table2.col2;
Cartesian product :

The Cartesian product is also referred as cross-join. It is a binary operation and is denoted by X. The degree of the new relation is the sum of the degrees of two relations on which Cartesian product is operated. The number of the tuples of the new relation is equal to the product of the number of tuples, of the two relations on which Cartesian product is performed.

Example 1:

Let A = {1,2} and B={a,b,c}, Then,
A×B = {(1,a),(1,b),(1,c),(2,a),(2,b),(2,c)}
⇒ Elements of A(3) * Elements of B(2) = Elements of [A×B] (6)

Example 2:

Consider two relations Person and Order such that

PERSON
P_ID Last_Name First_Name City
1 Sharma  Abhay Mumbai
2 Gupta  Mohan Delhi
3 Verma Akhil Mumbai
ORDERS
O_id Order_No P_id
1 10050 3
2 25000 3
3 5687 1
4 45000 1
5 35000 15
Query : SELECT Last_Name, First_Name, Order_No 
        FROM Person,Orders
        WHERE Person.P_ID = Orders.P_ID
        ORDER BY Person.Last_Name;

Solution :

Last_Name First_Name Order_No
Sharma Abhay 5687
Sharma Abhay 45000
Verma Akhil 10050
Verma Akhil 25000
Points
  • A Cartesian product is formed when join conditions are omitted and invalid.
  • All rows in the first table are joined to all rows in the second table.
  • If you want to avoid Cartesian product, then we should use a join condition in WHERE Clause.
  • Using Cartesian product, it gives us a large number of rows and is rarely useful.

Types of Equi-Joins

SQL Equi-Joins are further classified into two categories

  • Inner join
  • Outer join

Inner join

The Inner joins returns us the rows which are common in both the tables. i.e. gives the intersection of two tables.

Syntax :
SELECT col1,col2
FROM table1 INNER JOIN table2 
ON table1.column_name = table2.column_name;

Outer join

The outer join include rows in a joint result even when they have no match in the joint table.

And Outer join Returns all rows that must satisfy the join condition and also returns those rows from one table for which no rows from the other satisfy the join condition.

Left outer join:

The left outer join returns all the rows from the left table, even if there are no matches in the right table.

Example :
SELECT P.Lname, P.Fname, O.Orderno
FROM PERSON P
LEFT JOIN Orders O
ON P.PID = O.PID
ORDER BY P.Fname

Solution :

Last_Name First_Name Order_No
Sharma Abhay 5687
Sharma Abhay 45000
Verma Akhil 10050
Verma Akhil 25000
Gupta Mohan NULL
Right outer join:

The right outer join return all rows from the right table, even if there are no matches in the left table.

Syntax :
SELECT col1,col2
FROM table1
RIGHT JOIN table2
ON table1.col_name=table2.col_name;
Example :
SELECT P.Lname, P.Fname, O.Orderno
FROM Person P
RIGHT JOIN Order O
ON P.PID = O.PID
ORDER BY P.Lname;

Result :

Last_Name First_Name Order_No
Sharma Abhay 5687
Sharma Abhay 45000
Verma Akhil 10050
Verma Akhil 25000
NULL NULL 35000

Self join

A self join is a join in which the table is joined with itself to get the appropriate results. In this case, it is necessary to ensure that the join statement defines an ALIAS name for both the of copies of the tables to avoid column ambiguity.

Example :

Consider the relation Course as :

TABLE COURSE
Course_id Course_Name Pre_Course
1 C NULL
2 C++ 1
3 Java 2
4 C# 3
5 VB.NET 4
Query : SELECT A.Coursename AS Course, 
         B.Coursename AS Prerequisite_Course
        FROM Course A, Course B
        WHERE A.Precourse = B.CourseID;

Solution :

Course Prerequisite_Course
C++ C
Java C++
C# Java
VB.NET Java

Non Equi Join

Non Equi-Join is used to return the result from two or more tables where exact join is not possible. The SQL Non Equi-Join uses comparison operator instead of the equal sign like (>,<,>=,<=) along with conditions.

Syntax :
SELECT * FROM table1,table2
WHERE table1.column > table2.column;
Example :
SELECT E.Empno, E.Ename, E.Salary, S.Grade
FROM Emp E, SalaryGrade S
WHERE E.Salary BETWEEN S.LowSalary AND S.HighSalary;

Natural join

The natural join is a type of Equi Join and is structured in such a way that, columns with same name of associated tables will appear once only.

Syntax :
SELECT * FROM table1
NATURAL JOIN table2;
Example :
SELECT * FROM Person
NATULAR JOIN Orders;

Solution :

P_ID Last_Name First_Name City O_ID Order_No
1 Sharma  Abhay Mumbai 3 5687
1 Sharma  Abhay Mumbai 4 45000
3 Verma Akhil Mumbai 1 10050
3 Verma Akhil Mumbai 2 25000

Union

The union is used to return all the distinct rows selected by either query.

Example :
(SELECT Sname FROM Student 
WHERE Stream = 'Science') UNION 
(SELECT Sname FROM Student
WHERE Stream = 'Arts');

Summarization of Joins in SQL : Example :

Joins in SQL

 


Previous Home Next
Queries on SQL Operators The CREATE VIEW Command

     

Leave a Reply