SQL Cheat Sheet: JOIN statements
Joins
Topic Syntax Description Example
The CROSS JOIN is used to generate a paired
SELECT column_name(s) FROM table1 CROSS SELECT DEPT_ID_DEP, LOCT_ID FROM
Cross Join JOIN table2;
combination of each row of the first table with DEPARTMENTS CROSS JOIN LOCATIONS;
each row of the second table.
You can use an inner join in a SELECT select E.F_NAME,E.L_NAME, JH.START_DATE
SELECT column_name(s) FROM table1 INNER
from EMPLOYEES as E INNER JOIN JOB_HISTORY
Inner Join JOIN table2 ON table1.column_name = statement to retrieve only the rows that satisfy as JH on E.EMP_ID=JH.EMPL_ID where
table2.column_name; WHERE condition; the join conditions on every specified table. E.DEP_ID ='5';
The LEFT OUTER JOIN will return all records select
Left Outer SELECT column_name(s) FROM table1 LEFT
E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from
OUTER JOIN table2 ON table1.column_name = from the left side table and the matching
Join table2.column_name WHERE condition;
EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS
records from the right table. AS D ON E.DEP_ID=D.DEPT_ID_DEP;
select
SELECT column_name(s) FROM table1 RIGHT The RIGHT OUTER JOIN returns all records from E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from
Right Outer
OUTER JOIN table2 ON table1.column_name = the right table, and the matching records from EMPLOYEES AS E RIGHT OUTER JOIN
Join table2.column_name WHERE condition; DEPARTMENTS AS D ON
the left table.
E.DEP_ID=D.DEPT_ID_DEP;
The FULL OUTER JOIN clause results in the
Full Outer SELECT column_name(s) FROM table1 FULL inclusion of rows from two tables. If a value is select E.F_NAME,E.L_NAME,D.DEP_NAME from
OUTER JOIN table2 ON table1.column_name = EMPLOYEES AS E FULL OUTER JOIN DEPARTMENTS
Join table2.column_name WHERE condition; missing when rows are joined, that value is AS D ON E.DEP_ID=D.DEPT_ID_DEP;
null in the result table.
SELECT B.* FROM EMPLOYEES A JOIN EMPLOYEES
SELECT column_name(s) FROM table1 T1, A self join is regular join but it can be used
Self Join B ON A.MANAGER_ID = B.MANAGER_ID WHERE
table1 T2 WHERE condition; to joined with itself. A.EMP_ID = 'E1001';
Joins in MySQL using phpMyAdmin
SELECT column_name(s) FROM table1 LEFT
OUTER JOIN table2 ON table1.column_name = select E.F_NAME,E.L_NAME,D.DEP_NAME from
table2.column_name WHERE condition EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS
AS D ON E.DEP_ID=D.DEPT_ID_DEP
UNION
Full Outer The UNION operator is used to combine the UNION
Join SELECT column_name(s) result-set of two or more SELECT statements.
FROM table1 select E.F_NAME,E.L_NAME,D.DEP_NAME
RIGHT OUTER JOIN table2 from EMPLOYEES AS E
ON table1.column_name = table2.column_name RIGHT OUTER JOIN DEPARTMENTS AS D ON
WHERE condition E.DEP_ID=D.DEPT_ID_DEP
Author(s)
D.M Naidu
