SQL JOINS –
SQL joins is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are as follows:
· INNER JOIN
· LEFT OUTER JOIN
· RIGHT OUTER JOIN
· FULL OUTER JOIN
INNER OUTER JOIN - Inner join requires each row in the two joined tables to have matching column values. Inner join result is created by combining two tables based on join condition. Each row of Table 1 is compared against each row of Table 2 to find all matched rows. All the unmatched rows are discarded from both the tables.
e.g.
Employee table
Emp_id | Emp_name | Job_name | Dept_id |
123 | Devid | President | 101 |
124 | Tim | Manager | 101 |
125 | Maria | Manager | 102 |
126 | Jonas | Admin | 102 |
127 | Tina | Support | 104 |
Department table
Dept_id | Dept_name | Dept_location |
101 | Sales | NY |
102 | HR | CA |
103 | Marketing | NJ |
RIGHT OUTER JOIN – Right outer is used when you need all the records from right table and only matching record from left table. Each row of Table 1 is compared against each row of Table 2 to find all matched rows. Result set of right outer join contains matched and unmatched records from right table. Unmatched records from left table are discarded.
LEFT OUTER JOIN – Left outer is used when you need all the records from left table and only matching record from right table. Each row of Table 1 is compared against each row of Table 2 to find all matched rows. Result set of left outer join contains matched and unmatched records from left table.Unmatched records from right table is discarded.
FULL OUTER JOIN – Full outer join is used when you want matching and non-matching records from both tables. Each row of Table 1 is compared against each row of Table 2 to find all matched and unmatched records.