If you do the math, you can see why this is a very dangerous join to run against large tables. This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. However, sometimes you have a complicated query written and find you need an outer join and it is sometimes simpler to use the right join rather than reorder the tables in a complicated join. I think what youre looking for is to do a LEFT JOIN starting from the main-table to return all records from the main table regardless if they have valid data in the joined ones (as indicated by the top left 2 circles in the graphic). 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 It is true you can get away without the right join as it can always be expressed as a left join if you reverse the tables. To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause. To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause. If there is no match, the right side will contain null. Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the missing side will contain null. Inner join produces only the set of records that match in both Table A and Table B.įull outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. 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 nifty Venn diagrams. We'll populate them with four records each. The effect is a cross join.Similarly, you can use an on clause with cross join, which also differs from standard SQL. This is different from the ANSI standard and different from almost any other database. Table A is on the left, and Table B is on the right. When using join or inner join, the on condition is optional. I love the concept, though, so let's see if we can make it work. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |