October 3, 2011 at 10:33 am
I have table T1
1234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
October 3, 2011 at 10:46 am
October 3, 2011 at 11:08 am
What is the relationship (link) between your two tables?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 3, 2011 at 12:34 pm
hopefully this will give you some ideas.
pls post back if not undestood.
---=== use tempdb...nice safe place
USE [tempdb]
GO
---=== drop tables if they already exist....makes it easier for reruns in SSMS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1]') AND type in (N'U'))
DROP TABLE [dbo].[T1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T2]') AND type in (N'U'))
DROP TABLE [dbo].[T2]
GO
---=== create table T1
CREATE TABLE [dbo].[T1](
[ID] [int] NULL,
[COL_NAME] [varchar](10) NULL
) ON [PRIMARY]
GO
--=== add some data into T1
INSERT INTO [dbo].[T1]([ID], [COL_NAME])
SELECT 1, '1234' UNION ALL
SELECT 2, '2345' UNION ALL
SELECT 3, '3456'
SELECT ID, COL_NAME FROM T1 --- Show table data
---== create table T2
CREATE TABLE [dbo].[T2](
[ID] [int] NULL,
[COL_NAME] [varchar](10) NULL
) ON [PRIMARY]
GO
--=== add some data into T2
INSERT INTO [dbo].[T2]([ID], [COL_NAME])
SELECT 1, '4321' UNION ALL
SELECT 2, '5432' UNION ALL
SELECT 3, '6543'
SELECT ID, COL_NAME FROM T2--- Show table data
---==== based on the above table structures and your question...here follows a very simple method
---==== WE NEED A RELATIONSHIP (LINK) BETWEEN T1 and T2
---==== in this instance we are joining on the the ID columns of both tables
SELECT T1.COL_NAME + T2.COL_NAME AS AddCols
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 3, 2011 at 1:05 pm
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
I'm not sure that you understand a union in SQL. You are not showing me a result set, you are showing me a "calculation" based upon 2 values. A union would give the result set
1234
3456
Am I correct in assuming that you want value1 from T1 to be concatenated with value2 in T2 in such a way that it removes duplicate characters?
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 1:41 pm
Example tbale T1 has id 1 , 2, 3, 4
and T2 has id 3,4,5,6
so i want 1,2,3,4,5,6 as ID in result table
October 3, 2011 at 1:47 pm
If you don't mind using a temporary table, which can sometimes work very well and improve performance, you can do this:
SELECT c1, c2, c3, ..., cn
FROM T1
INTO #TEMP
GO
INSERT INTO #TEMP
SELECT c1, c2, c3, ..., cn
FROM T2
WHERE c1 NOT IN (SELECT c1 FROM #TEMP)
GO
SELECT * FROM #TEMP
This is the simplified version of course, and you can also use a NOT EXISTS instead of NOT IN. Also, depending on the amount of data, you may want to use a table variable instead of a temporary table.
Thanks,
Jared
Jared
CE - Microsoft
October 3, 2011 at 3:10 pm
komal145 (10/3/2011)
Example tbale T1 has id 1 , 2, 3, 4and T2 has id 3,4,5,6
so i want 1,2,3,4,5,6 as ID in result table
I think it is a language issue more than an understanding of union.
Your explanation left everybody scratching their head.
Table1 has a column ID with 4 rows and Table2 has a column ID with 4 rows? Yes a union is how you would do this.
select ID from table1
union
select ID from table2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2011 at 3:57 pm
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
Yes, several, but union (without ALL) would be best. Why do you ask?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 6:12 pm
ChrisM@home (10/3/2011)
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
Yes, several, but union (without ALL) would be best. Why do you ask?
This is not true. I have had sets of data where the UNION was killing the query to the point where I had to kill it. It was much faster to select the data I needed into a temporary table using several different queries and then selecting from that table. It is important to look at your execution plan and also test which is faster for you.
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 12:40 am
If the data volume is huge, UNION will certainly kill the performance.
October 4, 2011 at 2:33 am
jared-709193 (10/3/2011)
ChrisM@home (10/3/2011)
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
Yes, several, but union (without ALL) would be best. Why do you ask?
This is not true. I have had sets of data where the UNION was killing the query to the point where I had to kill it. It was much faster to select the data I needed into a temporary table using several different queries and then selecting from that table. It is important to look at your execution plan and also test which is faster for you.
Thanks,
Jared
Be careful with sweeping statements. If the OP's tables are relatively small then it's the best way to go. Having said that, look at the result of the following statements:
DROP TABLE #T1
DROP TABLE #T2
CREATE TABLE #T1 (ID INT)
CREATE TABLE #T2 (ID INT)
INSERT INTO #T1 (ID) VALUES (1),(2),(3),(4),(4)
INSERT INTO #T2 VALUES (3),(4),(5),(5),(6)
SELECT *
FROM #T1
UNION
SELECT *
FROM #T2
UNION dedupes #T1 (across the whole row) into the output, then adds rows from T2 which are not already in the output. That's a lot of checking - every row, every column has to be compared to the existing output before it can be added to it. The key point here is the whole row is checked - all of the columns selected from the input. It doesn't matter how you model this process, whether you spool intermediate results into a temp table or whatever, it's going to be equally as expensive as UNION because of the work involved.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2011 at 8:03 am
ChrisM@Work (10/4/2011)
jared-709193 (10/3/2011)
ChrisM@home (10/3/2011)
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
Yes, several, but union (without ALL) would be best. Why do you ask?
This is not true. I have had sets of data where the UNION was killing the query to the point where I had to kill it. It was much faster to select the data I needed into a temporary table using several different queries and then selecting from that table. It is important to look at your execution plan and also test which is faster for you.
Thanks,
Jared
Be careful with sweeping statements. If the OP's tables are relatively small then it's the best way to go. Having said that, look at the result of the following statements:
DROP TABLE #T1DROP TABLE #T2
CREATE TABLE #T1 (ID INT)
CREATE TABLE #T2 (ID INT)
INSERT INTO #T1 (ID) VALUES (1),(2),(3),(4),(4)
INSERT INTO #T2 VALUES (3),(4),(5),(5),(6)
SELECT *
FROM #T1
UNION
SELECT *
FROM #T2
UNION dedupes #T1 (across the whole row) into the output, then adds rows from T2 which are not already in the output. That's a lot of checking - every row, every column has to be compared to the existing output before it can be added to it. The key point here is the whole row is checked - all of the columns selected from the input. It doesn't matter how you model this process, whether you spool intermediate results into a temp table or whatever, it's going to be equally as expensive as UNION because of the work involved.
You mentioned that UNION would be best. I am countering that with "not always." Maybe you misunderstood my example... If you want values from T1 and T2 to be brought together assuming that all data in the resulting columns are already the same, a UNION would be horrible on performance because of exactly what you mentioned. If, however, I just want to see a list of identities from T1 combined with the identities in T2 without duplicates I may choose the query below:
CREATE TABLE #Tablename (ID INT)
INSERT INTO #Tablename
SELECT ID
FROM T1
INSERT INTO #Tablename
SELECT ID
FROM T2
WHERE ID NOT IN (SELECT ID FROM #Tablename)
SELECT *
FROM #Tablename
I understand that performance wise, in this 1 column example, a UNION will prove just as good. However, I have found this "method" extremely useful (without the NOT IN) when there is no possible way that the 2 tables have the SAME data, but you want them returned as 1 result. The UNION can hinder performance by comparing rows that you already know are not the same. Performance may vary when using a temp table versus a table variable.
I try to look at the performance of UNION first, but in this case when the question was specifically asking for a replacement to a UNION I wanted to give an answer not knowing how it will be used in practice.
Thanks,
Jared
Jared
CE - Microsoft
October 4, 2011 at 8:19 am
jared-709193 (10/4/2011)
...If, however, I just want to see a list of identities from T1 combined with the identities in T2 without duplicates I may choose the query below:
CREATE TABLE #Tablename (ID INT)
INSERT INTO #Tablename
SELECT ID
FROM T1
INSERT INTO #Tablename
SELECT ID
FROM T2
WHERE ID NOT IN (SELECT ID FROM #Tablename)
SELECT *
FROM #Tablename...
Better still, you would use this, which is logically the same as UNION without ALL:
CREATE TABLE #Tablename (ID INT)
INSERT INTO #Tablename
SELECT DISTINCT ID
FROM T1
INSERT INTO #Tablename
SELECT DISTINCT ID
FROM T2
WHERE ID NOT IN (SELECT ID FROM #Tablename)
SELECT *
FROM #Tablename
😉
I totally agree with most of your comments - testing the whole query and parts of it for performance, breaking queries up using temp tables when they outperform other options, etc - but I still disagree that in this simple case, with as yet no hint of huge tables, UNION wouldn't be the best choice. It's the best choice based on the information we've got.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply