SQL SERVER - JOINS - III

       

The SELF JOIN

The JOIN operator can be used to combine any pair of tables, including combining the table to itself. This is the "self join". Self joins can use any JOIN operator.
For instance, check this classical example of returning an employee's boss (based on Table1). In this example, we consider that the value in field2 is in fact the boss' code number, therefore related to key1.

SELECT t1.key1, t1.field1 as Name, 
   t1.field2, mirror.field1 as Boss
FROM Table1 t1

   LEFT JOIN Table1 mirror ON t1.field2 = mirror.key1;

And this is the output to this query.

key1	Name	field2	Boss
3	Erik	8	Harry
4	John	3	Erik
6	Mark	3	Erik
7	Peter	8	Harry
8	Harry	0	null

In this example, the last record shows that Harry has no boss, or in other words, he is the #1 in the company's hierarchy.

Excluding the Intersection of the Sets

Checking the previous Venn diagrams I just showed above, one may come to a simple question: what if I need to get all records from Table1 except for those that match with records in Table2. Well, this is pretty useful in day-to-day business, but obviously we don't need a special JOIN operator to do it.
Observe the result sets above and you will see you only need to add a WHERE clause to your SQL statement, looking for records that have a NULL value for Table2's key. So, the result set we are looking is the red area shown in the Venn diagram below Figure.

Non-matching records from Table1.

We can write a LEFT JOIN for this query, for instance:

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, 
   t2.key2 as T2Key, t2.field1 as City
FROM Table1 t1
   LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 
WHERE t2.key2 IS NULL;

And, finally, the result set will be:

key1	Name	T1Key	T2Key	City
6	Mark	7	null	null
7	Peter	8	null	null

When we do this kind of query, we have to pay attention to which field we pick for the WHERE clause. We must use a field that does not allow NULL values. Otherwise the result set may include unwanted records. That's why I suggested to use the second table's key. More specifically, its primary key. Since primary keys don't accept NULL values, they will assure our result set will be just what we needed.

One Word about Execution Plans

These comments lead us to an important insight. We usually don't stop to think about this, but observe that the execution plan of SQL queries will first calculate the result set for the FROM clause and the JOIN operator (if any), and then the WHERE clause will be executed.
This is as true for SQL Server as any other RDBMS.
Having a basic understanding of how SQL works is important for any DBA or developer. This will help you to get things done. In fast and reliable way. If you are curious about this, just take a look in the execution plan for the query above, shown in below figure.

The execution plan to a query using LEFT JOIN

Joins and Indexes

Take a look again at the Execution Plan of that query. Notice it used the clustered indexes of both tables. Using indexes is the best way to make your query run faster. But you have to pay attention to some details.
When we write our queries, we expect the SQL Server Query Optimizer to use the table indexes to boost your query performance. We can also help the Query Optimizer choose the indexed fields to be part of your query.
For instance, when using the JOIN operator, the ideal approach is to base the join condition over indexed fields. Checking again the Execution Plan, we notice that the clustered index on Table2 was used. This index was automatically built on key2 when this table was created, as key2 is the primary key to that table.
On the other hand, Table1 had no index on field key2. Because of that, the query optimizer tried to be smart enough and improve the performance of querying key2 using the only available index. This was the table clustered index, based on key1, the primary key on Table1. You see the query optimizer is really a smart tool. But you would help it a lot creating a new index (a non-clustered one) on key2.
Remembering a bit about referential integrity, you see key2 should be a foreign key on Table1, because it is related to another field in other table (which is Table2.key2).
Personally I believe foreign keys should exist in all real-world database models. And it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys.
(IMPORTANT: SQL Server will automatically create a clustered index on primary keys. But, by default, it does nothing with foreign keys. So make sure you have the proper settings on your database).

Non-equal Comparisons

When we write SQL statements using the JOIN operator, we usually compare if one field in one table is equal to another field in the other table. But this is not the mandatory syntax. We could use any logical operator, like different than (<>), greater than (>), less than (<) and so on.
Although this fancy stuff might give you the impression that SQL gives so much power, I feel this is more like a cosmetic feature. Consider this example. See Table 1 above , where we have 5 records. Now let's consider the following SQL statement.

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, 
   t2.key2 as T2Key, t2.field1 as City
FROM Table1 t1
   INNER JOIN Table2 t2 ON t1.key2 <= t2.key2
WHERE t1.key1 = 3 ;

Notice this uses an inner join and we are specifically picking one single record from Table1, the one where key1 is equal to 3. The only problem is that there are 6 records and Table2 that satisfy the join condition. Take a look in the output to this query.

key1	Name	T1Key	T2Key	City
3	Erik	1	1	New York
3	Erik	1	2	Sao Paulo
3	Erik	1	4	Paris
3	Erik	1	5	London
3	Erik	1	6	Rome
3	Erik	1	9	Madrid

The problem with non-equal joins is that they usually duplicate records. And this is not something you will need in a regular basis. Anyway, now you know you can do it.

Multiple JOINs

SQL JOINs are always about putting together a pair of tables and finding related objects that obey a given rule (usually, but not limited to, equal values). We can join multiple tables. For instance, to combine 3 tables, you will need 2 joins. And a new join will be necessary for each new table. If you use a join in each step, to combine N tables, you will use N-1 joins.
One important thing is that SQL allows you to use different types of joins in the same statement.
But DBAs and developers have to be careful on joining too many tables. Several times, I have seen situations where queries demanded 10, 20 tables or even more. For performance reasons, it is not a good idea to do a single query to put all data together. The query optimizer will do a better job if you break your query it into several smaller, simpler queries.
Now consider we have a third table, called Table3, shown bellow.

Table3

key3	field1
1	Engineer
2	Surgeon
3	DBA
4	Lawyer
5	Teacher
6	Actor

Now let's write a statement to bring the employee's name, the city where he lives and what is his profession. This will demand us to join all 3 tables. Just remember that joins are written in pairs. So first we will join Table1 to Table2. And then we will join Table1 and Table3. The resulting script is shown below.

SELECT t1.key1, t1.field1 as Employee, 
     t2.key2, t2.field1 as City,
     t3.key3, t3.field1 as Profession
FROM Table1 t1
     INNER JOIN Table2 t2 ON t1.key2 = t2.key2
     INNER JOIN Table3 t3 ON t1.key3 = t3.key3;

As we are running only INNER JOINs, we will have only records that match the combination of the 3 tables. See the output below.

key1	Name	key2	City	key3	Profession
3	Erik	1	New York	6	Actor
4	John	4	Paris	4	Lawyer
6	Harry	9	Madrid	2	Surgeon

 

Beyond the SELECT statements

The use of JOIN operators is not restricted to SELECT statements. In T-SQL, you can use joins in INSERT, DELETE and UPDATE statements as well. But keep in mind that in most RDBMS's we have nowadays, joins are not supported on DELETE and UPDATE statements. So I would advise you to restrict the use of joins to SELECT and INSERT statements only, even in SQL Server code. This is important if you want to keep your scripts more easily portable to different platforms.

Source: SQL Server Central