Good methods or tutorials for understanding JOINs in SQL

Question

Hi All,

As we know that there are different types of JOINs in any RDBMS, for eg:- Left join, outer join, inner join, left outer join etc. Invariably we use JOINs for lots of our business logics in our projects. However very few people have complete understanding or mastery over these JOINs. Usually people with half known knowledge on JOINs tend to get confused with complex queries. My area of interest would be mostly related to SQL Server since we are using that in almost all our projects.

I have gone through some nice articles on JOINs but most of them tend to confuse rather than convince. So I wanted to know if there are any good ways/methods or any good tutorials for getting a good grasp on JOINs in SQL. Tutorials should be simple enough as well as help in getting our basics on JOINs stronger.

Thanks in advance.

Answer 1

I find this link visually displaying the different joins in Venn diagrams is pretty succinct.

...Left join, outer join, inner join, left outer join...

LEFT JOIN and LEFT OUTER JOIN are the same; most databases just allow you to omit the OUTER keyword.

Answer 2

I don't have any particular tutorials in mind, but maybe think of this.

When you join a table, by default you're doing an inner join. This means you will get back a new table with the columns from the original two tables, and only the rows that match on the conditions you specified.

When left join, which is the same as left outer join, you are getting all of the rows from the left table (ex: leftTable left join rightTable), and only the rows that match the conditions you specified on the right table. For rows on the right table that don't satisfy your joining conditions, you will get back null data on the right.

Right joins are hardly ever used, but they're just like left joins except the return all rows from the right, and only the rows that match you join conditions on the left.

Full outer joins will show all of the data from both tables, and null on either side if it doesn't match the join condition. Think of it as more of a (leftTable | bothTables | rightTable) sort of a layout.

Answer 3

I find that along with books and articles it helps to tinker. When you want to do that you might want to take a look at what's here. Lots (currently > 13,000 working examples) that you are free to copy and change.

Answer 4

I don't have any articles but I can recommend two good books: SQL for Mere Mortals and Inside Microsoft SQL Server 2008 T-SQL querying. The first book is great at explaining the set theory behind joins and the latter book is great (literally the first chapter) in explaining how SQL Server processes a query. Also, regarding the latter book, see the chart mentioned in this article (the article is decent too!): http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Answer 5

Aside from the join types I use a clear consistent code layout which helps me simplify complex joins.

As an example:

SELECT Col1, Col2
FROM
    Customer AS C

        INNER JOIN
    Order AS O
        ON C.CustomerID = O.CustomerID

        INNER JOIN
    OrderItem AS OI
        ON O.OrderID = OI.OrderID

        LEFT JOIN
    OrderSomething OS
        ON C.CustomerID = OS.CustomerID
           AND O.OrderID = OS.OrderID

As you can see the source tables are clearly visible in a single column, the details of the join are kept with each table, and because I never use a RIGHT JOIN, the ON clauses only refer to tables above the clause.

This helps me think about with joins one at a time, and removes the complexity and confusion that would otherwise surround them. It takes a minute extra to write, but every time I have to maintain or modify the code it takes a minute less to understand it again.