Joins
<< CSQL Aggregate Functions And Grouping - TOC - Integrity Contraints >>
Join is a query that combines data from more than one table by means of single statements. Joining is done in SQL by specifying the tables to be joined in the FROM clause. Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. When you join two tables a Cartesian product is formed.
Types of Joins:
- Equi Join or Inner Join
- Non Equi Join
- Self Join
- Left Outer Join
Equi Join or Inner Join
An equi join is a join with a join condition containing an equality operator. It combines rows that have values based on the operator for the specified columns. It is also called simple join and inner join.
Example: To list the employee name, salary and department name
SELECT ename, dname, sal FROM emp,dept WHERE emp.deptno = dept.deptno;
The above query can be written using INNER JOIN keyword.
SELECT ename,dname,sal FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
Note: Null is an unknown and not a value. Null values never satisfy equi join condition.
What is Table Alias: Table alias can be used instead of table names to shorten codes. It is given as table name followed by a space and then the alias to be given in the FROM clause, and must replace the table name throughout the statement. Follow the below examples which is based on table alias.
Non Equi Joins
When the comparison operator used in joining columns is other than equality, the join is called a Non Equi join.
Example: To find the grade of employees based on their salary
SELECT e.ename, e.sal, s.grade FROM emp as e, salgrade as s WHERE e.sal between s.losal AND s.hisal;
In the above query, salgrade table has the details of grades according to salary.
Self Join
A self-join is a join of a table to itself. The table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. The table rows are combined and the rows, which satisfy the join condition, are returned.
Example: To get the names and salaries of all employees with their managers’ name.
SELECT a.ename, a.sal, b.ename FROM emp as a, emp as b WHERE a.mrg = b.empno;
In the above query, you might not get some records for some employees because they may not have managers. This happens for the top most employees in an organization.
Left Join
The left outer join (or simply left join) extends the result of a simple join. The result of left join for table emp and dept, always contains all the records of the “left” table(emp), even if the join-condition does not find any matching record in the "right" table (dept). This means that if the ON clause matches 0 (zero) records in dept, the join will still return a row in the result—but with NULL in each column from dept. Thus, left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).
Example: To get all the names, salary, department names of all employees, even those without a department.
SELECT ename, sal, dname FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno ;
<< CSQL Aggregate Functions And Grouping - TOC - Integrity Contraints >>