SQL JOINS

Join is a concept which is used to extract data from multiple SQL tables. The tables however should have a relationship.

Joins are categorized into 4 different types:

  • EQUIJOIN
  • NON-EQUIJOIN
  • INNER JOIN
  • OUTER JOIN

Equijoin

It is a type of join which is used to extract data from multiple tables whenever a common attribute is available. It is also known as 'natural join'. Equijoin always uses the equality operator (=) in the join condition.

Syntax:
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2 ... TABLE2.COLUMN1, TABLE2.COLUMN2 ... N
FROM TABLE1, TABLE2 ... N
WHERE TABLE1.COLUMN = TABLE2.COLUMN;

Note: Table name prefixed with the column name is optional, but it becomes mandatory whenever data is fetched from a common column. Check the examples below for clear understanding.

Example

Consider the tables shown below. The table EMP and DEPT have a common attribute called 'DEPTNO'. Equijoin can be performed on these tables to extract data from these tables.

SQL Equijoin Example
//Display EMPNO, NAME, SALARY, DEPTNO and LOCATION of all employee
SELECT EMP.EMPNO, EMP.NAME, EMP.SAL, DEPT.DNAME, DNAME.LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

//Prefixing column name is mandatory for column 'DEPTNO', since both 'EMP' and 'DEPT' 
//have a column named 'DEPTNO'. SELECT EMPNO, NAME, SAL, EMP.DEPTNO, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; //Display EMPNO, NAME, SALARY, DEPTNAME, LOCATION and XNAME of all employee SELECT EMPNO, NAME, SAL, LOC, DNAME, XNAME FROM EMP, DEPT, X WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.XID=X.XID;

Note: The result of a join statement will have that many number of rows as are there in the master table. The master table is the one which comes first after the 'FROM' keyword. In the above examples EMP is the master table ( FROM EMP, DEPT ..) and so the queries will return 5 records each.

Below are the results of the above queries:

SQL Equijoin
SQL Equijoin Result 1 and 2
SQL Equijoin Result 3

Non-Equijoin

Non-Equi join is used to extract data from multiple tables whenever no common attribute/ column is available but there is a relationship between the tables. Non-Equi join never uses the equality operator (=) in the join condition.

Example

There is no common column in the EMP and SAL_GRADE table shown below. However there is a relationship between them, the 'SAL' column of the EMP table has a relation with the 'LOW_SAL' and 'HIGH_SAL' of SAL_GRADE table. So non-equi join can be used to extract data from these tables.

SQL Non-Equijoin Example
//Display EMPNO, NAME, SAL and GRADE of all employee
SELECT EMPNO, NAME, SAL, GRADE
FROM EMP, SAL_GRADE
WHERE SAL BETWEEN LOW_SAL AND HIGH_SAL;

//Display EMPNO, NAME, SAL, GRADE, DNAME AND LOC of all employee
SELECT EMPNO, NAME, SAL, GRADE, DNAME, LOC
FROM EMP, SAL_GRADE, DEPT
WHERE SAL BETWEEN LOW_SAL AND HIGH_SAL
AND EMP.DEPTNO=DEPT.DEPTNO;

Note: The 2nd example uses equijoin as well as non-equijoin.

SQL Non-Equijoin
SQL Non-Equijoin Result

Inner Join

Inner Join is a type of join where two tables are joined to return those records that satisfy a given condition. It is a combination of Equijoin and Non-Equijoin.

Self Join

Self Join is an implementation of Inner Join. As the name suggest, self-join is used to join a table with itself. In this case the same table would act as a master as well as a child.

Example:

Below table has a relationship with itself. We can use self join on this table.

SQL SelfJoin
SELECT W.NAME || ' IS WORKING UNDER ' || M.NAME
FROM EMP W, EMP M
WHERE W.MGR=M.EMPNO;

The above query will produce output in the following format

  • XX is working under YY
  • YY is working under ZZ etc.

Here, W and M are considered as logically different tables, W is Worker and M is Manager.


Outer Join

Outer join returns all rows from at least one of the tables mentioned in the FROM clause, as long as those rows meet any search conditions.

Outer Join can be of 3 types:

  • Left Outer Join or Left Join
  • Right Outer Join or Right Join
  • Full Outer Join or Full Join
Left Outer Join

Left Outer Join will return all rows from the left table and will have null values from the right table. The right side table will be the deficient table in this case.

Example:

Considering the below two tables EMP and DEPT:

SQL Outer Join Example
SELECT EMPNO, NAME, SAL, DNAME, LOC
FROM EMP left outer join DEPT
on EMP.DEPTNO =DEPT.DEPTNO;

The above query would give the following output:

SQL Left Outer Join
Right Outer Join

Right Outer Join will return all rows from the right table, and will have null values for the left table. The left side table will be the deficient table in this case.

SELECT EMPNO, NAME, SAL, DNAME, LOC
FROM EMP right outer join DEPT
on EMP.DEPTNO =DEPT.DEPTNO;

Below is the result of the right outer join query:

SQL Right Outer Join
Full Outer Join

Full Outer Join will return all rows from both the tables depending on the condition. Both the tables are deficient in case of full outer join.

SELECT EMPNO, NAME, SAL, DNAME, LOC
FROM EMP full outer join DEPT
on EMP.DEPTNO =DEPT.DEPTNO;

Below is the result of the full outer join query:

SQL Full Outer Join

Cartesian Product

Cartesian product is an output of the sql query which we get whenever a condition is omitted from the join statement or join condition is treated as invalid. Cartesian product is also called as crossjoin.

SELECT EMPNO, NAME, SAL, DNAME, LOC
FROM EMP, DEPT;

A crossjoin always produce 'm x n' number of records, where m and n are the number of records in the two tables involved in the crossjoin.

POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article