SQL JOINS Explained

What are SQL JOINS?
SQL joins are widely used during different types of application development as well as for reporting and Business Intelligence process. We already know that the database consists of various tables. Not every data is stored in a single table so it is very crucial for a business to derive information from all the data sources. Now the 1/2 million-dollar question will be to figure out how to combine those tables and return the data that will be meaningful to the users. The answer is very simple and I lied above by saying 1/2 million dollar question. In SQL, we use JOIN clause to combine two or more tables. While using join, it is very important to keep in mind that table that we combine together needs to have at least one common column.There are several types of JOINS we can use. We will talk about it later in my blog. First of all let’s start with the INNER JOIN which is a simple join and used most frequently.

What is Inner Join?
When you use inner Join to combine multiple tables then it will return the entire row from the tables where common column from both the tables are matched.

Syntax:
Select <column List>
From < Table A> AS A
INNER JOIN  <Table B> AS B
ON A.CommonField = B.CommonFiled

Now, let me explain how this syntax can be used to return the desired data by combining two tables. We will use ADVENTURE WORKS database for demonstration purposes. First of all let’s say that we want to join two tables named Person.Person and Person.EmailAdress. By Joining this two-table sour desired result will be to get EmployeeName, BussinessEntiityID and Email Address. Here BussinessEntityID is common column between both the tables. Following Query will return the expected result from combining both the tables.

Syntax: Inner Join                                                                                                                           

Diagram: Inner Join

What is LEFT OUTER JOIN or LEFT JOIN?
Left Outer join is another type of JOIN that can be used to join multiple tables. Left Outer Joins returns all the records from the left (first) table and only matching records from the right (second) table. Can you now guess what RIGHT Join returns?

Syntax:
Select <column List>
From < Table A> AS A
LEFT OUTER JOIN  <Table B> AS B
ON A.CommonField = B.CommonFiled                                                                                               

Again, lets use the ADVENTURE WORKS Database for example. In this case when we use Left outer Join no matter what it will return all the rows from the left table but only rows that are matched will be returned from second table. So the bottom line on this example is all the fields from bussinessEntityID and Employee will be returned but the Email address column will only return if there is a match.

Syntax: Left Outer Join                                              

Diagram: Left Outer Join

What is RIGHT OUTER JOIN or RIGHT JOIN?
Right Outer join is another type of JOIN that can be used to join multiple tables. Right Outer Join returns all the records from the right (Second) table and only matching records from the left (first) table.

Syntax:
Select <column List>
From < Table A> AS A
RIGHT OUTER JOIN  <Table B> AS B
ON A.CommonField = B.CommonFiled

Again lets use the ADVENTURE WORKS Database for example. In this case when we use Right outer Join no matter what it will return all the rows from the right table but only rows that are matched will be returned from first table.
So the bottom line on this example is all the fields from EmailAddress Column will be returned but the rows that matches with Email. EmailAddress table will only be returned from the Person table.

Syntax: Left Outer Join

Diagram: Left Outer Join

What is Full Join?
Full Joins, also called as Full Outer Join returns all the records from both the tables regardless of whether there are matching records in the tables or not.

Syntax:
Select <column List>
From < Table A> AS A
FULL JOIN  <Table B> AS B
ON A.CommonField = B.CommonFiled

Syntax: Full Join

Diagram: Full Join

What is Cross Join?
 Cross-join will return all the possible combination of the rows from table 1 and table 2. For example if we have 3 rows in the table 1 and 3 rows in the table 2 then the cross join of this two table will result in 9 rows. 

Syntax:
Select  <column List>
From Table 1
CROSS JOIN
Table 2

History On Join Syntax                                                                                                                            
This is the original way to join two tables
SELECT S.idcust, 
       S.namecust, 
       L.idinvc, 
       L.amtinvchc 
FROM   inc61m.dbo.arcus S, 
       inc61m.dbo.arobl L 
WHERE  S.idcust = L.idcust 

If you forget to use the join predicate while using older syntax, you end up with an unintentional cross join. As you use the newer syntax, an inner join is not a valid syntax without an ON clause. Therefore, if you forget to indicate the join predicate, the parser will generate an error. This is very expensive for any database engine to process when you have big tables.

Best Practices
Remember to use regular identifier such as, letters,numbers and underscores for a schema, table, or column name, even though you can embed special characters such as @,# and $ in an identifiers. Avoid the old style join syntax. Some people find it difficult to adopt the ANSI SQL-92 syntax for joins with JOIN keyword and  the ON clause (e.g FROM T1 INNER JOIN T2 ON T1.KEYCOL = T2.KEYCOL), as opposed  to using a comma between the table names and then all predicates in the WHERE clause(e.g FROM T1,T2 WHERE T1.KEYCOL = T2.KEYCOL).

I encourage you to use the newer syntax (although contrary to popular belief, the old style syntax for the cross and inner joins is actually standard and is fully supported by SQL Server, Oracle and most databases in the market). Outer Join never had standard old style syntax; only cross and inner joins did. Outer joins have only one standard syntax,which is the ANSI SQL-93 syntax, outer joins are the reasons the standard bother creating a new syntax. For consistency's sake ,the standard also adopted newer syntax for the cross join and inner joins, although it didn't deprecate the old style syntax for both joins.

At any rate there are two main reasons why you should stick to the newer syntax for all joins.One is consistency, it simply doesn't look good when you try mixing the different styles. Another is the fact that if you use the old style syntax and forget to indicate the WHERE clause with the join predicate, you get an unintentional cross join. With the newer syntax,if you use an inner join forget the ON clause, you get a parsing error that forces you to fix the code.

Sub query can easily be converted to join and sub query are very expensive compared to join for any database engine to execute. You can rewrite your sub query to use join to increase the performance and reduce the overhead. Knowing the Index well will help get more bang for the buck on a Join. 

Thanks for taking the time to read my  blog post. Please share with our reader on any new ways of writing queries that you may have and remember to write what works best for you and your db engines.