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:
- INNER JOIN
- OUTER JOIN
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.
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.
//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:
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.
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.
//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.
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 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.
Below table has a relationship with itself. We can use self join on this table.
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 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.
Considering the below two tables EMP and DEPT:
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:
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:
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:
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.