Feeds:
Posts
Comments

Archive for the ‘SQL Joins’ Category

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

Posted in PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 3, 2013 | 5 Comments »

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.

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

About these ads

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 30, 2013 | 11 Comments »

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)

Read Full Post »

SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement

Posted in PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 27, 2012 | 9 Comments »

There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them.

Let us see the questions here.

  • I want to delete from multiple table in a single statement how will I do it?
  • I want to update multiple table in a single statement how will I do it?

The answer is – No, You cannot and you should not.

SQL Server does not support deleting or updating from two tables in a single update. If you want to delete or update two different tables – you may want to write two different delete or update statements for it. This method has many issues – from the consistency of the data to SQL syntax.

Now here is the real reason for this blog post – yesterday I was asked this question again and I replied my canned answer saying it is not possible and it should not be any way implemented that day. In the response to my reply I was pointed out to my own blog post where user suggested that I had previously mentioned this is possible and with demo example. Let us go over my conversation – you may find it interesting. Let us call the user DJ.

DJ: Pinal, can we delete multiple table in a single statement or with single delete statement?
Pinal: No, you cannot and you should not.
DJ: Oh okey, if that is the case, why do you suggest to do that?
Pinal: (baffled) I am not suggesting that. I am rather suggesting that it is not possible and it should not be possible.
DJ: Hmm… but in that case why did you blog about it earlier?
Pinal: (What?) No, I did not. I am pretty confident.
DJ: Well, I am confident as well. You did.
Pinal: In that case, it is my word against your word. Isn’t it?
DJ: I have proof. Do you want to see it that you suggest it is possible?
Pinal: Yes, I will be delighted too.
(After 10 Minutes)
DJ: Here are not one but two of your blog posts which talks about it -

Pinal: Oh!
DJ: I know I was correct.
Pinal: Well, oh man, I did not mean there what you mean here.
DJ: I did not understand can you explain it further.
Pinal: Here we go.

The example in the other blog is the example of the cascading delete or cascading update. I think you may want to understand the concept of the foreign keys and cascading update/delete. The concept of cascading exists to maintain data integrity. If there primary keys get deleted the update or delete reflects on the foreign key table to maintain the key integrity and data consistency. SQL Server follows ANSI Entry SQL with regard to referential integrity between PrimaryKey and ForeignKey columns which requires the inserting, updating, and deleting of data in related tables to be restricted to values that preserve the integrity. This is all together different concept than deleting multiple values in a single statement.

When I hear that someone wants to delete or update multiple table in a single statement what I assume is something very similar to following.

DELETE/UPDATE Table 1 (cols) Table 2 (cols)
VALUES … which is not valid statement/syntax as well it is not ASNI standards as well.

I guess, after this discussion with DJ, I realize I need to do a blog post so I can add the link to this blog post in my canned answer. Well, it was a fun conversation with DJ and I hope it the message is very clear now.

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

Read Full Post »

SQL SERVER – INNER JOIN Returning More Records than Exists in Table

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Joins, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on February 9, 2012 | 18 Comments »

I blog and engage with the community because it gives me satisfaction when someone resolves an issue. A few days ago, I blogged about a DBA who began his first day at a new company and could not find out where the installation summary file was. He was very happy when I featured his story on our blog. Today he asked me another question and when I received his question my first reaction was – not possible. Later I said, may be possible, and when he shared more information, I said of course it is possible and natural. Let us go over these conversations in the exact order of how they occurred.

(I have modified the emails to chat format as it is easier to understand)

Jeff: Pinal, help needed. My query is returning more data than I need.
Pinal: Well, check your WHERE clause, JOINS etc.
Jeff: The problem is that INNER JOIN is returning more records than exist in the table.
Pinal: Not possible. How can a query return data that does not exist. Are you sure that you are connected to the correct database and running the query on the same server where you are validating the data?
Jeff: Yes, yes, everything is in the right place. Can Inner Join ever return more data than exists in the table?
Pinal: Well, maybe. Here is the story. Inner Join can for sure return more records than exists in the table, but it cannot return records that do not exist. Does that make sense?
Jeff: No.
Pinal: Okay, in simple words, if your table has three rows (values 1, 2, 3), your inner join can return 10 rows but it cannot return you the value 4 as part of the result.
Jeff: In simple English please…
Pinal: Honestly, I cannot make it more simple than what I said just now. Here is the example for you.
(Jeff goes through example and after few minutes)
Jeff: Exactly, I had no equal condition which did this. You helped me again.
Pinal: You’re welcome!
(After few minutes…)
Jeff: Pinal, help needed…

Well, this goes on. Anyway, here is the script that I sent him.

Let us create a scenario. We will create three sample tables and insert values in it.

USE TempDB
GO
-- Sample Table
CREATE TABLE FirstTable (Col1 INT)
CREATE TABLE SecondTable (Col1 INT)
CREATE TABLE ThirdTable (Col1 INT)
GO
INSERT INTO FirstTable (Col1)
VALUES (1), (2), (3), (NULL)
GO
INSERT INTO SecondTable (Col1)
VALUES (1), (2), (3), (NULL)
GO
INSERT INTO ThirdTable (Col1)
VALUES (2), (2), (2), (2), (2), (NULL)
GO

Here you will see the normal use of INNER JOIN where a query returns the results as the number of rows or fewer than the number of rows in the table. This happens because the data is unique in both of the tables.

Query 1: Inner Join with Equal to Sign

-- Query 1
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN SecondTable s ON s.Col1 = f.col1
GO

Query 2: Inner Join with Not Equal to Sign

Here is a query where the data is still unique (distinct) among tables but the result set contains more records than the numbers of rows the table contains. The condition <> (not equal to) returns the result which was excluded in the query where = (equal to) condition was used.

-- Query 2
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN SecondTable s ON s.Col1 <> f.col1
GO

Note: If you combine the data of Query 1 and Query 2, you will get the same result as CROSS JOIN of both the tables. If you want to understand further, read my earlier article Introduction to Join – Visual Explanation.

Query 3: Inner Join with Non-Distinct Row Table

Here is a query where data in another table is not unique and INNER JOIN returns more records than one of the tables.

-- Query 3
SELECT f.Col1 fCol1
FROM FirstTable f
INNER JOIN ThirdTable s ON s.Col1 = f.col1
GO

Summary

Inner Join can for sure return more records than the records of the table. Inner join returns the results based on the condition specified in the JOIN condition. If there are more rows that satisfy the condition (as seen in query 2), it will return you more results. Reference: Introduction to Join – Visual Explanation.

Well, feel free to send your questions or even solutions. If I find them interesting I will share them with everybody with due credit.

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

Read Full Post »

SQL SERVER – Quick Note about JOIN – Common Questions and Simple Answers

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 4, 2011 | 6 Comments »

This blog post is written in response to the T-SQL Tuesday post of JOIN. This is a very interesting subject. Years ago, I wrote my article about SQL SERVER – Introduction to JOINs – Basic of JOINs, ‑ till date, it is my most favorite article on the blog.

Today we are going to talk about join and lots of things related to the JOIN. I recently started office hours to answer questions and issues of the community. I receive so many questions that are related to JOIN. I will share few of the same over here. Most of them are basic, but note that the basics are of great importance.

Without further ado, let me continue with the question and answers.

Q: Which one of the following is a better method?
Method 1:
SELECT t1.*, t2.*
FROM t1,t2
WHERE t1.col1 = t2.col1

Method 2:
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

A: The answer to this question will bring some interesting conversation. I strongly prefer method 2 because it is much cleaner to understand and if I have to use table level hints or so on, it is much convenient to do the same. I would suggest going ahead with method 2. Currently with regard to performance and execution plan, both the methods show the same (most of the time). However, with respect to standard and future innovation, method 2 is the way to go.

When I have to perform a performance tuning task, and if I see method 1, I usually ask the developer to convert it to method 2 as I feel much more comfortable with method 2. Additionally, when you have to work with OUTER JOIN, you will have to do so.

Q: What is better ‑ subquery or JOIN?
Subquery:
SELECT t1.*
FROM t1
WHERE t1.col1 IN (SELECT t2.col1 FROM t2)

Join:
SELECT t1.*
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

In this case, there is no right answer. You should use the one that gives you optimal performance. I have seen cases when the subquery gives optimal performance as well join giving optimal performance when compared to each other. I have seen either of them performing so well that I think one has to test out both the methods before selecting one. If you are facing situation where you are not sure which method you should select, I suggest that you go with your intuition. I still prefer JOIN over any other method, but in this case, I will suggest you to test your options.

Q: How to simulate Join?
A: I get this question a lot of times, and I have no answer. Here, I want your help as I do not even understand this question.

Q: How can I change my LEFT JOIN to RIGHT JOIN and get the same answer?
A: Sure. Here is quick example of the same:

Left Join:
SELECT t1.col1, t2.col2
FROM t1 LEFT JOIN t2 ON ON t1.col1 = t2.col1

Right Join:
SELECT t1.col1, t2.col2
FROM t2 RIGHT JOIN t1 ON ON t1.col1 = t2.col1

Both of the above options will give you same result. However, the real question is why you want to do that. What is the reason that you want to change the left join to right join?

Q: Does it matter how I write tables in my join if I am using INNER JOIN only?
A: No it does not matter in case of INNER JOIN as the result will be the same, and the SQL Server Engine will figure out the optimal execution plans for your query. As your question clearly suggests that for any other kind of join (i.e., OUTER JOIN, CROSS JOIN), it will matter for sure. Additionally, there are cases with INNER JOIN ‑ when order is forced on them, they have shown a little performance enhancement. Here is a quick example of the same.

If you have attended my session of Virtual Tech Days few days ago, you would have seen the example of the how forceorder hint works.

Q: Is there a quick tutorial to Joins?
A: I have written an article on this subject earlier, and as I said earlier in this article, I personally like the same a lot. Here you can read about the same: Introduction to JOINs – Basic of JOINs.

Q: Is there any book available to learn T-SQL, which explains various concepts like this easily?
A: I am bit biased but you can read about my books over here.

Q: Is SELF JOIN is a type of INNER JOIN or OUTER JOIN?
A: In fact, it is both an inner as well as outer join. Self Join is a very interesting subject. Here is an interesting article that I have written earlier on this subject: SQL SERVER – The Self Join – Inner Join and Outer Join .

Q: In case of the OUTER JOIN, where should I put the condition?
A: This question requires a detailed answer, I have written a detailed blog post on this subject over here: How ON Clause Effects Resultset in LEFT JOIN .

Q: What is Optimal LEFT JOIN or NOT IN?
A: I personally prefer LEFT JOIN as I have seen LEFT JOIN doing better in many cases. Once again, I suggest you should test it with your query. Here is a quick example of the same: Differences Between Left Join and Left Outer Join.

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

Read Full Post »

Older Posts »