× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

SQL Joins Explained Visually

Michael G Schwern
Oct 12, 2016
<p>People have a lot of trouble with SQL joins, and I don't blame them. Thinking relationally takes some time to get used to. Worse, there are many kinds of joins: Left, right, outer, inner, excluding, and full. Knowing what these are can be really handy and let you write compact, efficient SQL queries. But I never remember them.</p> <p>Fortunately, <a href="http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins">C L Moffatt made a gorgeous visual guide to each of them</a>.</p> <p><img alt="Visual SQL Joins" src="http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg" style="height:760px; width:966px"></p> <p> </p> <p>Let's say you have a vehicles table, and a table for their registration info. You want all vehicles (A) which are registered (B). That's the intersection of A and B. Looking at our diagram, that's the middle one, an inner join. This is the default join.</p> <p><img alt="Inner join" src="http://www.codeproject.com/KB/database/Visual_SQL_Joins/INNER_JOIN.png" style="height:174px; width:258px"></p> <pre><code class="language-sql">select * from vehicles inner join registrations on registrations.vehicle_id = vehicles.id</code></pre> <p>What if you want all vehicles (A) that *aren't* registered (B)? That's A without B. This is the left excluding join. You get everything on the left that isn't also on the right.</p> <p><img alt="Left excluding join" src="http://www.codeproject.com/KB/database/Visual_SQL_Joins/LEFT_EXCLUDING_JOIN.png" style="height:174px; width:258px"></p> <pre><code class="language-sql">select * from vehicles left join registrations on registrations.vehicle_id = vehicles.id where registrations.vehicle_id is null</code></pre> <p><a href="http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins">C L Moffatt has a great article about this</a>. If you're learning SQL, I'd suggest you read it and bookmark it for future use. I use it all the time. <a href="http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins">http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins</a></p>
comments powered by Disqus