SQL Join

By ukmodak | June 6th 2022 12:23:03 PM | viewed 54 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;

bONEandALL
Visitor

Total : 9224

Today :9

Today Visit Country :

  • United States