5. Display Data from Multiple Tables
Joins
- A Join is used to combine columns from two or more tables based on values of the related columns.
- The related columns are typically the primary key column(s) of the first table and foreign key column(s) of the second table.
- The connection between the tables is established through the WHERE clause.
Types of Joins are:
- Equi join
- Cartesian join
- Outer join
- Self-join
- Non-equi join
Joins
Check out the following blog about Joins in SQL:
Syntax:
SELECT table1.column1, table2.column2 FROM table1, table2
WHERE table1.column1 = table2.column2
Note:
Write the join condition in the WHERE clause.
Prefix the column name with table name when the same column name appears in more than one table.
Equi Join/ Inner join
When two tables are joined together using the equality of values in one or more columns, they make an equi join. The equi join or Inner join will display the matching rows in two tables.
Ques. List the employees, Employee ID, Name, Department number and the department name.
SELECT empno, ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Note: DeptNo exists in both the tables, to avoid ambiguity, the column name should be qualified with the table name. Both table names/aliases should be specified (Emp and Dept). The WHERE clause defines the joining condition i.e. join the Emp table with the dept table based on DeptNo column. It checks for the equality of values in these columns.
Practice: Check the number of rows in the above query. Write a query to see all the departments in the department table and explain the difference in the number of rows.
Using Table Aliases
It can be tedious to type the complete Table names repeatedly. Just like columns, Aliases can be used for Table names too. Using table aliases also speeds up the query.
Practice: Write the above query using the table aliases
SELECT empno, ename, DeptNo, dname
FROM emp e, dept d
WHERE DeptNo = D. DeptNo;
Cartesian Join
When no WHERE clause is specified, each row of one table matches every row of the other table. This results in a cartesian product.
If table 1 has 10 rows, Table 2 has 10 rows, cartesian join will give you 100 rows.
SELECT empno, ename, e.deptno, dname
FROM emp e, dept d
The emp table has 14 rows, dept table has 4. The above query will give you 14 * 4 = 56 rows.
Outer Join
If there are any values in one table that do not have corresponding values in the other, in an equi join that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that row will have Nulls.
Ques. Display the employees working in each department. Display the department information even if no employees belong to that department.
SELECT empno, ename, e. DeptNo, d.deptno dept, dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;
The above SQL will display the department details with NULL for Employees.
If the plus sign (+) is placed on the other side, then the employee details with no corresponding department name will be displayed with NULL values in Department columns.
SELECT empno, ename, e.deptno, d.deptno dept, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);
Left and Right Outer Join
Left/Right outer joins are similar to Outer join. It will display the result of a join between two tables – Left and Right.
When using the left join, it will display the matching rows from the two tables and also all the rows present in the left table. The non-matching rows in the right table will be displayed as null.
SELECT empno, ename, e.deptno, d.deptno, dname
FROM emp e
LEFT JOIN dept d ON d.deptno = e.deptno;
Right join is opposite of left join i.e. all matching rows and all the rows from the right table will be displayed, any non-matching rows in the left will be displayed as NULLs.
SELECT empno, ename, e.deptno, d.deptno, dname
FROM emp e
RIGHT JOIN dept d ON d.deptno = e.deptno;
Self-Join
A table can join to itself too i.e., each row in a table is combined with itself and with every other row of the table. The self-join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining table to itself is almost the same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual table names are used, since both the tables have the same name.
Ques. Display the Employee name and his manager details.
SELECT Worker.empno , Worker.ename, Manager.empno as Manager_ID, Manager.ename as Manager
FROM emp Worker, emp Manager
WHERE Worker.mgr = Manager.mgr;