8

This is my query

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1

It is working properly.

Now I want to fetch the table name for the respective productName in future.

  1. So how can I fetch it?

I tried AS.. like this:

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;

but didn't get output.

  1. How shall I correct the query and also Improve the performance of query?
  • 1
    Just add some identifier SELECT ...blah,'tablename' FROM t1 and so on – Mihai Sep 10 '14 at 11:14
20

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to:

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;

This will return a new column called Source with an identifier that shows which table the row came from.

As @ypercube suggested in the comments you may also want to consider altering this to a UNION ALL - this will include duplicate but you'll also have an identifier on which table it came from. Using a UNION ALL will eliminate the performance impact of removing dups.

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;
  • @bluefeet: How i can improve the performance of query? – Prashant Tapase Sep 10 '14 at 11:51
  • @Prashant I'm guessing part of your issue is with the UNION, see my edit using UNION ALL. The UNION is going to remove duplicates but it results in additional overhead. – Taryn Sep 10 '14 at 12:24
3

You can try this with UNION ALL

as probably you need duplicate entry too as will adding table name it will be unique and performance will be also improved

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as TableName
FROM tablename1 
where Active =1 
UNION ALL
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as TableName
FROM tablename2 
where Active =1
UNION ALL
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as TableName
FROM tablename3 
where Active =1;
0

the below query works better and gives you better idea as above queries don't render rows twice.

SELECT t1.*,t1.table_name
FROM
t1
UNION ALL
SELECT t2.*,t2.table_name
FROM
t2
LEFT OUTER JOIN 
t1
ON t1.id=t2.id
WHERE t1.id=null

the above script don't give duplicate data rows and make sure

  1. All common data bteween t1 and t2 as source t1 table.
  2. Non t2 rows source as t1 table .
  3. Non t1 rows source as t2 table.

if you want common data from t2 table , flip left join

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.