CODEWITHSUNDEEP
sqlunionsybase
Laura Smith
Laura Smith

Reputation: 313

Compare two tables with different column numbers in sql

I am using Sybase for my SQL coding.

I was comparing tables which have the same columns such as follows:

SELECT name, date, time, location
FROM
(SELECT * FROM table1
UNION ALL  
SELECT * FROM table2) data
GROUP BY name, date, time, location
HAVING count(*)!=2

Now, I want to be able to compare the table1 and table2 but now table2 has another column called origin and I am not sure on how to extend my current logic to make it happen.

---Intention: to be able to compare the two tables with varying column numbers
---How to modify this code to do it?

I want to be able to show the differences between the two tables after the query.

May someone guide me? I dont want to use joins or minus, I prefer to use the UNION way.

Upvotes: 0

Views: 209

Answers (2)

shawnt00
shawnt00

Reputation: 17915

I think the problem is that you actually want to ignore the extra column. The problem with using select * is that things can change.

SELECT name, date, time, location
FROM (
    SELECT name, date, time, location FROM table1
    UNION ALL  
    SELECT name, date, time, location FROM table2
) data
GROUP BY name, date, time, location
HAVING count(*) != 2

If you're not going to use select * in one half of the union there isn't any reason include origin in the first place.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

If you want to union two different tables, you must make up missing columns. E.g.:

SELECT name, date, time, location, origin FROM table2
UNION ALL
SELECT name, date, time, location, null as origin FROM table1

Upvotes: 1

Related Questions