SQL Joins
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.There are different types of joins available in SQL:
- INNER JOIN: returns rows when there is a match in both tables.
- LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
- FULL JOIN: returns rows when there is a match in one of the tables.
- SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
To use JOIN in a query statement, the query writer must know the structure of the tables. There must be at least one column of data between the two tables which matches for a JOIN to work properly. This information must be provided to the SQL server as part of the query.
Generally, the data involved in a JOIN operation is composed of a “primary” key value in one table and a “foreign” key value in another. A primary key is usually a unique identity value in a table. A foreign key exists in a table which is in some way related to the unique record in the table holding the primary key.
Left JOIN and Right JOIN
There are multiple types of JOIN statements. Each has a slightly different function. JOIN commands refer to the order in which the data is merged together. One of the most common JOIN statements is a LEFT JOIN. In a LEFT JOIN, all data from the table with the primary key value are returned as well as rows from the table holding the foreign key if there is a match. Rows in the foreign key table that do not match a value in the primary key table are ignored.
A RIGHT JOIN is very similar to a LEFT JOIN. As the name suggests, this JOIN type returns the exact opposite result of a LEFT JOIN. The RIGHT JOIN will return all rows from the table holding the foreign key in addition to rows from the primary key table that match. Rows from the primary key table that do not have a corresponding foreign key value are ignored.
Inner, Full, and Cross JOIN
An INNER JOIN statement will compare the data in both tables and return only those rows from each table where a match has occurred. Rows in both the primary key table and foreign key table that do not have a corresponding row in the other table will be ignored. A FULL JOIN is the opposite of an INNER JOIN. A JOIN of this variety returns all rows from each table, aligning data when a match exists.
Another variety of JOIN types, though used less commonly, is the CROSS JOIN. A CROSS JOIN ignores the key values provided and JOINS every row of the primary table with every row of the secondary table. This produces a Cartesian result set. This type of JOIN could be useful for viewing all possible combinations of cars and car paint colors, as an example.
Examples
SELECT e.last_name, e.first_name, e.salary,d.department_id
FROM employees e, departments d
where e.department_id = d.department_id
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
SELECT e.last_name, e.first_name, e.salary,d.department_id
FROM employees e full outer join departments d
on e.department_id = d.department_id
SELECT e.last_name, e.first_name, e.salary,d.department_id
FROM employees e , departments d
where e.department_id(+) = d.department_id
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
Self Joins e.g
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;