SQL Using Joins

Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world SQL usage you will often need to get data from multiple tables in a single query. Oracle provides the facility to retrieve the data from multiple tables with the help of joins. It is perhaps a wonderful feature of SQL that permit to retrieve the data from multiple users.

A join is used to combine rows from multiple tables. Join is performed whenever two or more tables is listed in the FROM clause of an SQL statement. The sql JOIN clause is used whenever we have to select data from 2 or more tables. The sql JOIN clause is used to retrieve data from 2 or more tables joined by common fields. The most common scenario is a primary key from one of the tables matches a foreign key in second table.

A join is a mechanism that allows the tables to be linked together. The rows retrieved after joining the two tables based on a condition in which one table act as a primary table and other act as a foreign table. The columns in the first table which act as a foreign key must match to the column in the second table which is defined as a primary key referenced by foreign key.

Syntax: SELECT table1.column, table2.column, ... From <table1>, <table2>Where table1.column=table2.column and more conditions;
Now we are going to create the tables for various operations:
SQL > DESC DEPT;
SQL > DESC EMP;

For example In the DEPT table, DEPTNO column act as a primary key and table act as a primary table (Parent table). In the EMP table EMPNO act as a primary key DEPTNO act as foreign key referencing by primary key and table act as Child table. We can use the common column .i.e. DEPTNO to retrieve the information from both tables.

There are various categories of Joins:

Equi Join

Cartesian Join

Outer Join

Self Join