|
== Self-join ==
La self-join serve a unire una tabella con se stessa.
A self-join is joining a table to itself.<ref>{{Harvnb|Shah|2005|p=165}}</ref> This is best illustrated by the following example.
=== Example ===
A query to find all pairings of two Impiegatis in the same country is desired. If you had two separate tables for Impiegatis and a query which requested Impiegatis in the first table having the same country as Impiegatis in the second table, you could use a normal join operation to find the answer table. However, all the Impiegati information is contained within a single large table. <ref> Adapted from {{harvnb|Pratt|2005|pp=115–6}}</ref>
Considering a modified <code>Impiegati</code> table such as the following:
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+Impiegati Table
|-
! ImpiegatiID !! Cognome !! Country !! ID_dipartimento
|-
| 123 || Rossi || Australia || 31
|-
| 124 || Bianchi || Australia || 33
|-
| 145 || Mancini || Australia || 33
|-
| 201 || Santoro || United States || 34
|-
| 305 || Monti || United Kingdom || 34
|-
| 306 || Grassi || United Kingdom || {{null result}}
|}
<br clear="all" />
An example solution query could be as follows:
<source lang=sql>
SELECT F.ImpiegatiID, F.Cognome , S.ImpiegatiID, S.Cognome , F.Country
FROM Impiegati F, Impiegati S
WHERE F.Country = S.Country
AND F.ImpiegatiID < S.ImpiegatiID
ORDER BY F.ImpiegatiID, S.ImpiegatiID;
</source>
Which results in the following table being generated.
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
|+Impiegati Table after Self-join by Country
|-
! ImpiegatiID !! Cognome !! ImpiegatiID !! Cognome !! Country
|-
| 123 || Rossi || 124 || Bianchi || Australia
|-
| 123 || Rossi || 145 || Mancini || Australia
|-
| 124 || Bianchi || 145 || Mancini || Australia
|-
| 305 || Monti || 306 || Grassi || United Kingdom
|}
<br clear="all" />
For this example, note that:
*<code>F</code> and <code>S</code> are [[alias (SQL)|aliases]] for the first and second copies of the Impiegati table.
*The condition <code>F.Country = S.Country</code> excludes pairings between Impiegatis in different countries. The example question only wanted pairs of Impiegatis in the same country.
*The condition <code>F.ImpiegatiID < S.ImpiegatiID</code> excludes pairings where the <code>ImpiegatiID</code>s are the same.
*<code>F.ImpiegatiID < S.ImpiegatiID</code> also excludes duplicate pairings. Without it only the following less useful part of the table would be generated (for the United Kingdom only shown):
{| class="wikitable" style="text-align:center; float:left; margin-right:5px"
! ImpiegatiID !! Cognome !! ImpiegatiID !! Cognome !! Country
|-
| 305 || Monti || 305 || Monti || United Kingdom
|-
| 305 || Monti || 306 || Grassi || United Kingdom
|-
| 306 || Grassi || 305 || Monti || United Kingdom
|-
| 306 || Grassi || 306 || Grassi || United Kingdom
|}
<br clear="all" />
Only one of the two middle pairings is needed to satisfy the original question, and the topmost and bottommost are of no interest at all in this example.
== Alternatives ==
The effect of outer joins can also be obtained using [[correlated subquery|correlated subqueries]]. For example
<source lang=sql>
SELECT Impiegati.Cognome , Impiegati.ID_dipartimento, Dipartimenti.Nome_dipartimento
FROM Impiegati LEFT OUTER JOIN Dipartimenti
ON Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento
</source>
can also be written as
<source lang=sql>
SELECT Impiegati.Cognome , Impiegati.ID_dipartimento,
(SELECT Dipartimenti.Nome_dipartimento
FROM Dipartimenti
WHERE Impiegati.ID_dipartimento = Dipartimenti.ID_dipartimento )
FROM Impiegati
</source>
== Implementation ==
Much work in database-systems has aimed at efficient implementation of joins, because relational systems commonly call for joins, yet face difficulties in optimising their efficient execution. The problem arises because (inner) joins operate both [[commutative]]ly and [[associative]]ly. In practice, this means that the user merely supplies the list of tables for joining and the join conditions to use, and the database system has the task of determining the most efficient way to perform the operation. A [[query optimizer]] determines how to execute a query containing joins. A query optimizer has two basic freedoms:
# '''Join order''': Because joins function commutatively and associatively, the order in which the system joins tables does not change the final result-set of the query. However, join-order '''does''' have an enormous impact on the cost of the join operation, so choosing the best join order becomes very important.
# '''Join method''': Given two tables and a join condition, multiple [[algorithm]]s can produce the result-set of the join. Which algorithm runs most efficiently depends on the sizes of the input tables, the number of rows from each table that match the join condition, and the operations required by the rest of the query.
Many join-algorithms treat their inputs differently. One can refer to the inputs to a join as the "outer" and "inner" join operands, or "left" and "right", respectively. In the case of nested loops, for example, the database system will scan the entire inner [[table (database)|relation]] for each row of the outer relation.
One can classify query-plans involving joins as follows:<ref name=Yu1998>{{Harvnb|Yu|Meng|1998|p=213}}
</ref>
; left-deep : using a base table (rather than another join) as the inner operand of each join in the plan
; right-deep : using a base table as the outer operand of each join in the plan
; bushy : neither left-deep nor right-deep; both inputs to a join may themselves result from joins
These names derive from the appearance of the [[query plan]] if drawn as a [[Tree data structure|tree]], with the outer join relation on the left and the inner relation on the right (as convention dictates).
=== Join algorithms ===
Three fundamental algorithms exist for performing a join operation.
==== Nested loops ====
{{main2|Nested loop join|block nested loop}}
Use of nested loops produces the simplest join-algorithm. For each [[tuple]] in the outer join relation, the system scans the entire inner-join relation and appends any tuples that match the join-condition to the result set. Naturally, this algorithm performs poorly with large join-relations: inner or outer or both. An [[index (database)|index]] on columns in the inner relation in the join-predicate can enhance performance.
The block nested loops (BNL) approach offers a refinement to this technique: for every block in the outer relation, the system scans the entire inner relation. For each match between the current inner tuple and one of the tuples in the current block of the outer relation, the system adds a tuple to the join result-set. This variant means doing more computation for each tuple of the inner relation, but far fewer scans of the inner relation.
==== '''Merge join'''====
If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus:
:# Consider the current "group" of tuples from the inner relation; a group consists of a set of contiguous tuples in the inner relation with the same value in the join attribute.
:# For each matching tuple in the current inner group, add a tuple to the join result. Once the inner group has been exhausted, advance both the inner and outer scans to the next group.
Merge joins offer one reason why many optimizers keep track of the sort order produced by query plan operators—if one or both input relations to a merge join arrives already sorted on the join attribute, the system need not perform an additional sort. Otherwise, the DBMS will need to perform the sort, usually using an [[external sort]] to avoid consuming too much memory.
{{seealso|Sort-Merge Join}}
==== Hash join ====
{{main|Hash join}}
A hash join algorithm can only produce equi-joins. The database system pre-forms access to the tables concerned by building [[hash table]]s on the join-attributes. The lookup in hash tables operates much faster than through index trees. However, one can compare hashed values only for equality, not for other relationships.
== See also ==
* [[SQL]]
* [[Query optimizer]]
* [[Composition of relations]]
== Notes ==
{{reflist|2}}
== References ==
*{{citation
|last= Pratt
|first= Phillip J
|title= A Guide To SQL, Seventh Edition
|publisher= Thomson Course Technology
|year= 2005
|isbn= 9780619216740
}}
*{{citation
|last=Shah
|first= Nilesh
|title= Database Systems Using Oracle - A Simplified Guide to SQL and PL/SQL Second Edition
|edition= International Edition
|publisher= Pearson Education International
|origyear= 2002
|year= 2005
|isbn= 0131911805
}}
*{{citation
|url= http://books.google.com/books?id=aBHRDhrrehYC
|accessdate= 2009-03-03
|title= Principles of Database Query Processing for Advanced Applications
|last = Yu
|first= Clement T.
|last2= Meng
|first2= Weiyi
|year= 1998
|publisher= Morgan Kaufmann
|isbn= 9781558604346
}}
== External links ==
* [http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ SQL SERVER - Introduction to JOINs - Basic of JOINs]
* [http://www.sqldatabasetutorial.com/sqltutorial/sqljoin-innerjoin.aspx SQL Inner Join with visual explanation]
* [http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug138.htm Sybase ASE 15 Joins]
* [http://dev.mysql.com/doc/refman/5.0/en/join.html MySQL 5.0 Joins]
* [http://www.oraclepassport.com/OracleJoins.html Oracle Joins - Quick Reference]
* [http://www.postgresqlguide.com/retrieving-data-from-multiple-tables-by-using-sql-join.aspx PostgreSQL Join with Query Explain]
* [http://www.postgresql.org/docs/8.3/static/tutorial-join.html PostgreSQL 8.3 Joins]
* [http://msdn2.microsoft.com/en-us/library/ms191517.aspx Joins in Microsoft SQL Server]
* [http://maxdb.sap.com/currentdoc/45/f31c38e95511d5995d00508b5d5211/content.htm Joins in MaxDB 7.6]
* [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm Joins in Oracle 11g]
* [http://www.partow.net/programming/databasealgos/index.html Various join-algorithm implementations]
* [http://www.codinghorror.com/blog/archives/000976.html A Visual Explanation of SQL Joins]
* [http://www.halfgaar.net/sql-joins-are-easy Another visual explanation of SQL joins, along with some set theory]
* [http://www.gplivna.eu/papers/sql_join_types.htm SQL join types classified with examples]
* [http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx An alternative strategy to using FULL OUTER JOIN]
{{databases}}
[[Category:SQL keywords]]
[[Category:Articles with example SQL code]]
[[cs:JOIN]]
[[es:Join]]
[[fr:Jointure (informatique)]]
[[no:Join (SQL)]]
[[ru:Алгоритм соединения (СУБД)]]
[[uk:Об'єднання (SQL)]]
[[vi:Join (SQL)]]
| 