SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”. 

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant – they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
GO
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;

Execution Plan:

Example 2: With CTE

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN. 

Here is one of the my favorite videos on this subject:

http://www.youtube.com/watch?v=Z7VuYBXX2dg

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

MySQL – How to do Natural Join in MySQL? – A Key Difference Between Inner Join and Natural Join

As you know MySQL supports ANSI JOINs like INNER JOIN, OUTER JOIN, CROSS JOIN etc. Do you know that it supports NATURAL JOIN?. A NATURAL JOIN is a type of JOIN which automatically maps the similar columns from both the tables.

Let us create the following tables
CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

By using the NATURAL JOIN, we can select only distinct columns by using * in the SELECT statement.
SELECT * FROM items NATURAL JOIN sales;

The result is

item_id Item_description sales_id sales_date sales_amount
 1 Television 1 2014-01-01 00:00:00 1200.00
 2 Mobile 2 2014-01-02 00:00:00 200.00
 3 laptop 3 2014-01-09 00:00:00 1700.00
 3 laptop 4 2014-01-29 00:00:00 1700.00
 3 laptop 5 2014-02-11 00:00:00 1700.00
 1 Television 6 2014-02-16 00:00:00 1200.00
 2 Mobile 7 2014-02-16 00:00:00 200.00
 2 Mobile 8 2014-02-20 00:00:00 200.00
 2 Mobile 9 2014-02-20 00:00:00 200.00
 2 Mobile 10 2014-02-22 00:00:00 200.00
 3 laptop 11 2014-02-24 00:00:00 1700.00
 1 Television 12 2014-02-24 00:00:00 1200.00

As you have noticed, * does not return all the columns from both the tables. The common column item_id is chosen first, followed by other columns in the tables items and sales. But if you use INNER JOIN with SELECT * all the columns will be selected which will result to duplicate columns in the resultset.

You can also use the USING clause with INNER JOIN to produce the same result.

SELECT * FROM items INNER JOIN sales USING (item_id);

In the above statement item_id is used to map both the tables.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Example of Performance Tuning for Advanced Users with DB Optimizer

Performance tuning is such a subject that everyone wants to master it. In beginning everybody is at a novice level and spend lots of time learning how to master the art of performance tuning. However, as we progress further the tuning of the system keeps on getting very difficult. I have understood in my early career there should be no need of ego in the technology field. There are always better solutions and better ideas out there and we should not resist them. Instead of resisting the change and new wave I personally adopt it.

Here is a similar example, as I personally progress to the master level of performance tuning, I face that it is getting harder to come up with optimal solutions. In such scenarios I rely on various tools to teach me how I can do things better. Once I learn about tools, I am often able to come up with better solutions when I face the similar situation next time.

A few days ago I had received a query where the user wanted to tune it further to get the maximum out of the performance. I have re-written the similar query with the help of AdventureWorks sample database.

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID;

User had similar query to above query was used in very critical report and wanted to get best out of the query. When I looked at the query – here were my initial thoughts

  • Use only column in the select statements as much as you want in the application
  • Let us look at the query pattern and data workload and find out the optimal index for it

Before I give further solutions I was told by the user that they need all the columns from all the tables and creating index was not allowed in their system. He can only re-write queries or use hints to further tune this query.

Now I was in the constraint box – I believe * was not a great idea but if they wanted all the columns, I believe we can’t do much besides using *. Additionally, if I cannot create a further index, I must come up with some creative way to write this query. I personally do not like to use hints in my application but there are cases when hints work out magically and gives optimal solutions.

Finally, I decided to use Embarcadero’s DB Optimizer. It is a fantastic tool and very helpful when it is about performance tuning.

I have previously explained how it works over here.

First open DBOptimizer and open Tuning Job from File >> New >> Tuning Job.

Once you open DBOptimizer Tuning Job follow the various steps indicates in the following diagram.

Essentially we will take our original script and will paste that into Step 1: New SQL Text and right after that we will enable Step 2 for Generating Various cases, Step 3 for Detailed Analysis and Step 4 for Executing each generated case. Finally we will click on Analysis in Step 5 which will generate the report detailed analysis in the result pan.

The detailed pan looks like. It generates various cases of T-SQL based on the original query. It applies various hints and available hints to the query and generate various execution plans of the query and displays them in the resultant. You can clearly notice that original query had a cost of 0.0841 and logical reads about 607 pages. Whereas various options which are just following it has different execution cost as well logical read. There are few cases where we have higher logical read and there are few cases where as we have very low logical read.

If we pay attention the very next row to original query have Merge_Join_Query in description and have lowest execution cost value of 0.044 and have lowest Logical Reads of 29. This row contains the query which is the most optimal re-write of the original query. Let us double click over it.

Here is the query:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID
OPTION (MERGE JOIN)

If you notice above query have additional hint of Merge Join. With the help of this Merge Join query hint this query is now performing much better than before.

The entire process takes less than 60 seconds.

Please note that it the join hint Merge Join was optimal for this query but it is not necessary that the same hint will be helpful in all the queries. Additionally, if the workload or data pattern changes the query hint of merge join may be no more optimal join. In that case, we will have to redo the entire exercise once again. This is the reason I do not like to use hints in my queries and I discourage all of my users to use the same.

However, if you look at this example, this is a great case where hints are optimizing the performance of the query. It is humanly not possible to test out various query hints and index options with the query to figure out which is the most optimal solution. Sometimes, we need to depend on the efficiency tools like DB Optimizer to guide us the way and select the best option from the suggestion provided.

Let me know what you think of this article as well your experience with DB Optimizer. Please leave a comment.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL – Difference Between INNER JOIN and JOIN

Here is the follow up question to my earlier question SQL – Difference between != and Operator <> used for NOT EQUAL TO Operation. There was a pretty good discussion about this subject earlier and lots of people participated with their opinion. Though the answer was very simple but the conversation was indeed delightful and was indeed very informative.

In this blog post I have another following up question to all of you.

What is the difference between INNER JOIN and JOIN?

If you are working with database you will find developers use above both the kinds of the joins in their SQL Queries. Here is the quick example of the same.

Query using INNER JOIN

SELECT *
 FROM Table1
 INNER JOIN  Table2 ON Table1.Col1 = Table2.Col1

Query using JOIN

SELECT *
 FROM Table1
 JOIN  Table2 ON Table1.Col1 = Table2.Col1

The question is what is the difference between above two syntax.

Here is the answer – They are equal to each other. There is absolutely no difference between them. They are equal in performance as well as implementation. JOIN is actually shorter version of INNER JOIN.

Personally I prefer to write INNER JOIN because it is much cleaner to read and it avoids any confusion if there is related to JOIN. For example if users had written INNER JOIN instead of JOIN there would have been no confusion in mind and hence there was no need to have original question.

Here is the question back to you -

Which one of the following syntax do you use when you are inner joining two tables – INNER JOIN or JOIN? and Why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script to Update a Specific Column in Entire Database

Last week, I have received a very interesting question and I find in email and I really liked the question as I had to play around with SQL Script for a while to come up with the answer he was looking for. Please read the question and I believe that all of us face this kind of situation.

“Pinal,

In our database we have recently introduced ModifiedDate column in all of the tables. Now onwards any update happens in the row, we are updating current date and time to that field.

Now here is the issue, when we added that field we did not update it with a default value because we were not sure when we will go live with the system so we let it be NULL. Now modification to the application went live yesterday and we are now updating this field.

Here is where I need your help. We need to update all the tables in our database where we have column created ModifiedDate and now want to update with current datetime. As our system is already live since yesterday there are several thousands of the rows which are already updated with real world value so we do not want to update those values. Essentially, in our entire database where ever there is a ModifiedDate column and if it is NULL we want to update that with current date time? 

Do you have a script for it?”

Honestly I did not have such a script. This is very specific required but I was able to come up with two different methods how he can use this method.

Method 1 : Using INFORMATION_SCHEMA

SELECT 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='ModifiedDate'
WHERE T.TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME;

Method 2: Using DMV

SELECT 'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' + t.name + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name ='ModifiedDate'
ORDER BY SCHEMA_NAME(t.schema_id), t.name;

Above scripts will create an UPDATE script which will do the task which is asked.

We can pretty much the update script to any other SELECT statement and retrieve any other data as well.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com

SQL SERVER – DELETE From SELECT Statement – Using JOIN in DELETE Statement – Multiple Tables in DELETE Statement

This blog post is inspired from my earlier blog post of UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement. In the blog post I discussed about how we can use JOIN and multiple tables in the UPDATE statement. There were plenty of the emails after this blog post discussing about using JOIN in the DELETE statement as well using multiple tables in the DELETE statement.

It is totally possible to use JOIN and multiple tables in the DELETE statement. Let us use the same table structure which we had used previously.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO

Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now pay attention to following diagram. Here we have two tables Table1 and Table 2. Our requirement is that we want to delete those two records from Table1 where Table2 Col3 values are “Two-Three” and “Two-Four” and Col1 in both the tables are the same.

I have explained the above statement very easily in following diagram.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the DELETE statement and use multiple tables in the DELETE statement and do the task.

-- Delete data from Table1
DELETE Table1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t2.Col3 IN ('Two-Three','Two-Four')
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

As you can see that using JOIN clause in DELETE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO

Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

Reference: Pinal Dave (http://blog.sqlauthority.com)