The syntax will vary depending on which database type you are using. For more details, see the documentation for your database, linked below.
In this example, I have a photo gallery database with two tables. One table is for Galleries and the other for Photos.
|6||Black and White|
Notice that in this dataset that there are no photos in the macro or black and white galleries, and that space.jpg is assigned to a gallery that doesn't exist in the Gallery table.
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.
The inner join is the default used when you don't specify the type of Join. When you do an inner join of two tables, it returns results which are matched in both tables.</p>
Full Outer Join
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 missing side will contain null.</p>
|NULL||NULL||6||Black and White||NULL|
Left Outer Join
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 right side will contain null.</p>
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.</p>
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.</p>
|NULL||6||Black and White||NULL||NULL|
Did you enjoy this post? Let others know!
If you enjoyed this post, please share it with others. Click one of the social media buttons below to share on Facebook, Twitter, LinkedIn, Pinterest or email to a friend.