- EQUI-JOIN: is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate.
- FULL JOIN: return all rows from both tables, regardless of whether or not the other table has a matching value.
- 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.
- LEFT EXCLUDE JOIN: return all of the records in the left table that do not match any records in the right table.
- RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
- RIGHT EXCLUDE JOIN: return all of the records in the right table that do not match any records in the left table.
- 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.
- UNION: Removes the duplicate rows.
- UNION ALL: Includes the duplicate rows.
- INTERSECT: Returns the common rows from the multiple queries.Includes the duplicate rowsRemoves the duplicate rows
- EXCEPT: Return rows present in first query which is not present in second query.
- CROSS JOIN: returns the cartesian product of the sets of records from the two or more joined tables. Return rows present in first query which is not present in second query - See more at: http://www.techvolcano.co.in/2013/05/oracle-sql-joins.html#sthash.ikxdhShX.dpufReturn rows present in first query which is not present in second query - See more at: http://www.techvolcano.co.in/2013/05/oracle-sql-joins.html#sthash.ikxdhShX.dpufReturn rows present in first query which is not present in second query - See more at: http://www.techvolcano.co.in/2013/05/oracle-sql-joins.html#sthash.ikxdhShX.dpufReturn rows present in first query which is not present in second query - See more at: http://www.techvolcano.co.in/2013/05/oracle-sql-joins.html#sthash.ikxdhShX.dpuf
EXAMPLE DATA BASE TABLE:
---------------------------------------------------------------------------------------------------------------------------
EQUI-JOIN: is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate.
SELECT *
FROM Employees JOIN Departments
FROM Employees JOIN Departments
ON Employees.EmployeeDepartmentID= Departments.DepartmentID;
or
SELECT *
FROM Employees, Departments
WHERE Employees.EmployeeDepartmentID= Departments.DepartmentID;
SELECT *
FROM Employees, Departments
WHERE Employees.EmployeeDepartmentID= Departments.DepartmentID;
---------------------------------------------------------------------------------------------------------------------------
FULL JOIN: return all rows from both tables, regardless of whether or not the other table has a matching value.
The FULL JOIN keyword combines the result of both LEFT and RIGHT joins.
FROM Employees FULL JOIN Departments
ON Employees.EmployeeDepartmentID = Departments.DepartmentID;
Where records in the FULL JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
---------------------------------------------------------------------------------------------------------------------------
INNER JOIN: returns rows when there is a match in both tables.
- "explicit join notation
- "implicit join notation".
The "explicit join notation" uses the
JOIN keyword, optionally preceded by the INNER keyword, to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Departments d INNER JOIN Employees e
ON d.DepartmentID = e.EmployeeDepartmentID
The "implicit join notation" simply lists the tables for joining, in the FROM clause of the SELECT statement, using commas to separate them. Thus it specifies a cross join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).
SELECT e.EmployeeId, e.EmployeeName, d.DepartmentName
FROM Departments d, Employees e
WHERE d.DepartmentID = e.EmployeeDepartmentID
---------------------------------------------------------------------------------------------------------------------------
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
SELECT *
FROM Employees LEFT JOIN Departments
ON employees.EmployeeDepartmentID = departments.DepartmentID;
FROM Employees LEFT JOIN Departments
ON employees.EmployeeDepartmentID = departments.DepartmentID;
The result of a left join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
This means that if the
ON clause matches 0 (zero) records
in B (for a given record in A), the join will still return a row in the
result (for that record)—but with NULL in each column from B.
---------------------------------------------------------------------------------------------------------------------------
LEFT EXCLUDE JOIN: return all of the records in the left table (table A) that do not match any records in the right table (table B).
SELECT *
FROM Employees LEFT JOIN Departments
ON employees.EmployeeDepartmentID = departments.DepartmentID
WHERE departments.DepartmentID IS NULL
FROM Employees LEFT JOIN Departments
ON employees.EmployeeDepartmentID = departments.DepartmentID
WHERE departments.DepartmentID IS NULL
---------------------------------------------------------------------------------------------------------------------------
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table (NULL in the case of no matching join predicate).
FROM Employees RIGHT OUTER JOIN Departments
ON Employees.EmployeeDepartmentID = Departments.DepartmentID;
---------------------------------------------------------------------------------------------------------------------------
RIGHT EXCLUDE JOIN: return all of the records in the right table (Table B) that do not match any records in the left table (Table A).
SELECT *
FROM Employees RIGHT OUTER JOIN Departments
ON Employees.EmployeeDepartmentID = Departments.DepartmentID
WHERE Employees.EmployeeDepartmentID IS NULL
---------------------------------------------------------------------------------------------------------------------------
FROM Employees RIGHT OUTER JOIN Departments
ON Employees.EmployeeDepartmentID = Departments.DepartmentID
WHERE Employees.EmployeeDepartmentID IS NULL
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.
SELECT
Emp1.EmployeeID AS 'Employee ID',
Emp1.EmployeeName AS 'Employee Name',
Emp2.EmployeeID AS 'Manager ID',
Emp2.EmployeeName AS 'Manager Name'
Emp1.EmployeeID AS 'Employee ID',
Emp1.EmployeeName AS 'Employee Name',
Emp2.EmployeeID AS 'Manager ID',
Emp2.EmployeeName AS 'Manager Name'
FROM Employees Emp1
INNER JOIN Employees Emp2 ON Emp1.ManagerID = Emp2.EmployeeID
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
Union, Intersection, and Except
Set operators combine results from two or more queries into a single result set.
UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.
( subquery ) UNION ( subquery )
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
( subquery ) EXCEPT ( subquery )
Example:
CROSS JOIN: returns the cartesian product of the sets of records from the two or more joined tables.
SELECT d.DepartmentID, d.DepartmentName, e.EmployeeId, e.EmployeeName, e.EmployeeDepartmentID
FROM Departments d CROSS JOIN Employees e
---------------------------------------------------------------------------------------------------------------------------
Resources:
http://swade016.wordpress.com/2010/10/07/chapter-7-sql-joins/
http://www.sqlpedia.pl/laczenie-tabel-sql/
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312-Different-Types-of-SQL-Joins.html
http://www.softwaretestingclass.com/sql-joins/
http://www.techonthenet.com/sql/joins.php
http://www.codeproject.com/Articles/485777/SQL-Joins
http://en.wikipedia.org/wiki/Join_%28SQL%29#Equi-join
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
http://www.funslate.net/Articles.aspx?ArtId=QL09
http://www.dotnet-tricks.com/Tutorial/sqlserver/W1aI140312-Different-Types-of-SQL-Joins.html
http://www.gplivna.eu/papers/sql_join_types.htm
http://www.tutorialspoint.com/sql/sql-using-joins.htm
Brak komentarzy:
Prześlij komentarz