nama column sama and

JOIN TYPE DESCRIPTION datatype sama EXAMPLE QUERY ada column yg sama
impli
dalam dua dua table
Joins two tables based on columns that have the
NATURAL JOIN SELECT department_name, city FROM departments NATURAL JOIN locations;
same name and data type.
datatype tak sama Used to specify several columns have the same
SELECT employee_id,first_name, last_name, department_name
antara A and B names but the data types do not match
FROM employees JOIN departments
Use the USING clause to match only one column
USING (department_id)
when more than one column matches.
nama column yang SELECT employee_id, last_name, location_id, department_id
USING sama FROM employees e JOIN departments d
Do not qualify a column that is used in the USING
USING (d.department_id) @
xboleh guna alias klu guna clause. If the same column is used elsewhere in
SELECT employee_id, last_name, location_id, e.department_id
USING the SQL statement, do not alias it.
FROM employees e JOIN departments d
USING (department_id)
Use ON when your join involves multiple
SELECT e.first_name, e.last_name, e.salary, d.department_name
conditions, comparisons, or functions beyond just
FROM employees e
ON = WHERE statement matching specific columns
JOIN departments d
- ada extra condition If there is no matching customer for an order, the ON e.employee_id = d.manager_id AND e.salary > 15000
columns from the customers table will be NULL.
SELECT worker.last_name emp, manager.last_name mgr
SELF-JOIN Joins a table to itself. FROM employees worker JOIN employees manager
ON worker.manager_id = manager.employee_id
SELECT e.last_name, e.salary, j.grade_level
Joins two tables based on a condition that does not FROM employees e
NONEQUIJOINS
involve equality. JOIN job_grades j
ON e.salary >= j.lowest_sal AND e.salary <= j.highest_sal
SELECT e.employee_id, e.first_name, e.last_name, department_name
Returns all rows from the left table, and matching left right
LEFT OUTER JOIN FROM departments d LEFT JOIN employees e
rows from the right table.
ON e.department_id = d.department_id
SELECT e.employee_id, e.first_name, e.last_name, department_name
Returns all rows from the right table, and matching
RIGHT OUTER JOIN FROM departments d RIGHT JOIN employees e
rows from the left table.
ON e.department_id = d.department_id ON f.manager_id = e.employee_id
SELECT e.employee_id, e.first_name, e.last_name, department_name
Returns all rows from both tables, regardless of
FULL OUTER JOIN FROM departments d FULL JOIN employees e
whether there is a match in the other table.
ON e.department_id = d.department_id
Returns every possible combination of rows from SELECT department_name, first_name, last_name FROM departments CROSS JOIN
Cartesian Product (CROSS JOIN)
two tables. employees

INNER JOIN = USING CLAUSE