SQL Join
By
ukmodak
|
March 31st 2024 10:32:28 AM
| viewed
765
times
|
Description |
Query |
| Inner join |
A join that returns only those rows that have a match in both joined tables |
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 INNER JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
|
| Outer join |
An outer join returns the rows even if they don't have related rows in the joined table. There are three types of outer joins: left outer join (or left join), right outer join (or right join), and full outer join (or full join). |
|
| Left Joins |
A LEFT JOIN statement returns all rows from the left table along with the rows from the right table for which the join condition is met. |
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_id;
|
| Right Joins |
It returns all rows from the right table along with the rows from the left table for which the join condition is met. |
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY dept_name;
|
| Full Joins |
A FULL JOIN returns all the rows from the joined tables, whether they are matched or not
Some databases, such as Oracle, MySQL do not support full joins. In that case you can use the UNION |
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;
|
| Cross Joins |
If you don't specify a join condition when joining two tables, database system combines each row from the first table with each row from the second table. This type of join is called a cross join or a Cartesian product. The following Venn diagram illustrates how cross join works. |
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 CROSS JOIN departments AS t2;
|
