How to Determine Which Columns Are Shared Between Two Tables

Finding common columns between multiple tables

The Query below does exactly the same thing. So you don't need an inner and outer query.

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS isc
  INNER JOIN @TEMPTABLE tt on tt.TableName = isc.TABLE_NAME
  GROUP BY COLUMN_NAME
  HAVING COUNT(*) > 1
  ORDER BY COLUMN_NAME

SQL find the same column in different tables

If you're looking for column names which are the same between two tables, this should work:

select name from syscolumns sc1 where id = object_id('table1') and exists(select 1 from syscolumns sc2 where sc2.name = sc1.name and sc2.id = object_id('table2'))

You could also make sure they're the same type by tossing in and sc1.xtype = sc2.xtype in the subquery.

Shared columns between two tables in DB2

I'm not sure why @SabirKhan answer has so many sub-queries -- just join the meta information to itself -- an inner join will ensure you get results from both tables.

SELECT A.COLNAME AS DUP 
FROM SYSCAT.COLUMNS A 
JOIN SYSCAT.COLUMNS B ON A.COLNAME = B.COLNAME AND  B.TABNAME='TABLE2' AND B.TABSCHEMA='BP' 
WHERE A.TABNAME='TABLE1' AND A.TABSCHEMA='BP' 

As for you not seeing answers that work with all database platforms. You are correct -- there are no such answers -- database platforms vary a lot.

SQL: Select like column from two tables

SELECT ColumnA FROM Table1 UNION Select ColumnB FROM Table2 ORDER BY 1

Also, if you know the contents of Table1 and Table2 will NEVER overlap, you can use UNION ALL in place of UNION instead. Saves a little bit of resources that way.

-- Kevin Fairchild

Select different columns from multiple tables based on common columns

Please use modern JOIN syntax rather than comma separated tables with WHERE.

So your SQL becomes

select t1.id, t1.idUnit, MAX(t1.date), t1.?, t2.? from table1 t1 INNER JOIN table2 t2 
ON t1.id = t2.id and t1.date = t2.date
GROUP BY t1.id, t1.idUnit, t1.?, t2.?

Note that you have to include in the GROUP BY all the other fields that you want in the SELECT (where I have t1.? and t2.? as place holders)

EDIT

Having seen the sample data, try the following. If you are using SQL Server you can copy the whole thing and paste into a query window. The same sort of sub-query approach as used below, can be done in any db.

declare @table1 table
(
id int,
idUnit int,
tDate datetime,
extraCol varchar(10)
)
declare @table2 table
(
id int,
tDate datetime,
extraCol varchar(10)
)
INSERT INTO @table1 VALUES(1, 1, '2017-01-23 01:00:00', 'a')
INSERT INTO @table1 VALUES(2, 1, '2017-01-23 02:00:00', 'b')
INSERT INTO @table1 VALUES(3, 2, '2017-01-23 01:00:00', 'c')
INSERT INTO @table1 VALUES(4, 2, '2017-01-23 02:00:00', 'd')

INSERT INTO @table2 VALUES(1, '2017-01-23 01:00:00', 'w')
INSERT INTO @table2 VALUES(2, '2017-01-23 02:00:00', 'x')
INSERT INTO @table2 VALUES(3, '2017-01-23 01:00:00', 'y')
INSERT INTO @table2 VALUES(4, '2017-01-23 02:00:00', 'z')

SELECT t1.id, t1.idUnit, t1.tDate, t1.extraCol, t2.extraCol FROM @table1 t1
INNER JOIN @table2 t2 ON t1.id = t2.id and t1.tDate = t2.tDate
INNER JOIN
(SELECT idUnit,  MAX(tDate) as maxDate FROM @table1 
GROUP By idUnit) m
ON t1.idUnit = m.idUnit and t1.tDate = m.maxDate
ORDER BY id

Find SQL rows that are not shared between two tables with identical fields

Using NOT IN:

SELECT a.column
  FROM TABLE_A a
 WHERE a.column NOT IN (SELECT b.column
                          FROM TABLE_B b)

Using NOT EXISTS:

This is a good one if you need to compare more than one column...

SELECT a.column
  FROM TABLE_A a
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_B b
                   WHERE b.column = a.column)

Using LEFT JOIN/IS NULL:

   SELECT a.column
     FROM TABLE_A a
LEFT JOIN TABLE_B b ON b.column = a.column
    WHERE b.column IS NULL

Because of the table aliases, you could swap the table names without changing the rest of the query to see the opposite--rows from TABLE_B that aren't in TABLE_A.

Is there a way in SQL Server to show the differences in fields between two tables that have an identical layout and share some common data

You can select the common materials from each organizations tables and add an organization pseudo column. Then just union the two queries together and sort them so the rows line up one after another.

DECLARE @og1Mats TABLE (
    Material INT, Description NVARCHAR(50), Color NVARCHAR(50), MSRP INT
)

DECLARE @og2Mats TABLE (
    Material INT, Description NVARCHAR(50), Color NVARCHAR(50), MSRP INT
)

INSERT INTO @og1Mats VALUES (11040, 'World Cup', 'black', 100),(11050, 'Fabric', 'yellow', 10),(11060, 'Steel', 'gray', 50);
INSERT INTO @og2Mats VALUES (11040, 'World Cup', 'black', 120),(11030, 'Concrete', 'gray', 10),(11060, 'Steel', 'black', 55);

WITH common AS (
    SELECT o1.Material FROM @og1Mats o1
    INNER JOIN @og2Mats o2 ON o1.Material=o2.Material
)
SELECT o1.*, 1 as organization
FROM @og1Mats o1
INNER JOIN common c ON c.Material=o1.Material
UNION ALL 
SELECT o2.*, 2 as organization
FROM @og2Mats o2 
INNER JOIN common c ON c.Material=o2.Material
ORDER BY
    Material
    , organization

That will get you an output similar to your example data with rows grouped by material and organization. You could then use that to compare the data either manually or with some other tool.

---

Related Topics