Explicación de join sql

Coding Horror: A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/archives/000976.html

programming and human factors by Jeff Atwood *

October 11, 2007
A Visual Explana on of SQL Joins
I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set‐based, the use of Venn diagrams to explain them seems, at firstblush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my tes ng. I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the le , and Table B is on the right. We'll populate them with four records each.
id -1 2 3 4 name ---Pirate Monkey NinjaSpaghetti id -1 2 3 4 name ---Rutabaga Pirate Darth Vader Ninja

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those ni y Venn diagrams.

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id -1 3 name ---Pirate Ninja id -2 4 name ---Pirate Ninja

Inner join produces only the set of records that match in both TableA and Table B.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name id -1 2 3 4 null null name ---Pirate Monkey Ninja Spaghetti null null id -2 null 4 null 1 3 name ---Pirate null Ninja null Rutabaga Darth Vader

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missingside will contain null.

1 of 29

30/11/2009 06:18 p.m.

Coding Horror: A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/archives/000976.html

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name id -1 2 3 4 name ---Pirate Monkey Ninja Spaghetti id -2 null 4 null name ---Pirate null Ninja null

Le outer join produces a complete set of recordsfrom Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id -2 4 name ---Monkey Spaghetti id -null null name ---null null

To produce the set of records only in Table A, but not in Table B, we perform the same le outer join, thenexclude the records we don't want from the right side via a where clause.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null id -2 4 null null name ---Monkey Spaghetti null null id -null null 1 3 name ---null null Rutabaga Darth Vader

To produce the set of records unique to Table A and Table B, we perform the same full outerjoin, then exclude the records we don't want from both sides via a where clause.

There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:
SELECT * FROM TableA CROSS JOIN TableB

This joins "everything to everything", resul ng in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a verydangerous join to run against large tables.

2 of 29

30/11/2009 06:18 p.m.

Coding Horror: A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/archives/000976.html

Database/SQL Tool For DB2, SQL Server, Derby, Mimer Informix, Oraclé and more
www.dbvis.com

Posted by Jeff Atwood View blog reac ons « A Lesson in Control Simplicity Mouse Ballis cs »

3 of 2930/11/2009 06:18 p.m.

Coding Horror: A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/archives/000976.html

Comments Hey Jeff, thanks for the great blog ‐ I thoroughly enjoy reading every single one of your posts. Even though I o en am familiar with the concepts you talk of, I find the simple manner in which you break down the issues is always a great refresher. Keep up the...