These are mainly used for retrieving information from more than one table. There are three types of joins:
1. Inner Join.
2. Outer Join.
3. Cross Join.
Extracts only matching records when the condition is true. An inner join has an ON clause for specifying the conditions. The keyword inner is optional because a JOIN clause will be INNER by default.
SELECT [colnames|*] FROM [table1] JOIN [table2] ON [condn]
SELECT * FROM emp e JOIN dept d ON e.deptno=d.deptno
In the above example, the matching records that are in both emp and dept tables are selected.
Though the condition is false, matching records will be displayed.i.e., if we want to include data rows in the result set that does not have a match in the joined table, we can create a join. When you create a outer join, the order in which the SQL statement appears is significant. The first table becomes Left Table. The second table becomes Right Table. There are three types of outer join:
Left Outer Join:
Choose the first table to create left outer join. Displays all records from left table and the matching records from the right table.
SELECT e.eid , d.deptno FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
Displays all records from emp and matching records from the dept table. If there is no matching deptno record for eid, then the row contains a NULL value in the deptno column.
Right Outer Join:
Choose the second table to create right outer join. Displays all records from right table and matching records from left table. The following example, displays all records from dept and matching records from the emp table. If there is no matching eid record for deptno, then the row contains a NULL value in the eid column.
SELECT e.eid , d.deptno FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno
Full Outer Join:
Choose the both tables to create full outer join. Displays matching and un matching records from both the tables.
SELECT e.eid , d.deptno FROM emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno
A cross join does not have any WHERE clause, produces the Cartesian product of the tables that are involved in the joins. The size of the Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
SELECT e.eid , d.deptno FROM emp e CROSS JOIN dept d