Display records from table1 and records from table2 which does not exist in table 1

Question

I've two table which contains three property in each. I want to display all records from table 1 and in table 2 extract only the records which do not exist in table 1.

table 1

  ID   Percentage  OrderDate
+----+------------+----------+
  1      2.0       2015-05-08
  1      5.0       2014-05-08 
  1      19.65     2013-05-08
  1      5.06      2012-05-08
  1      98.0      2011-05-08
  1      8.56      2010-05-08
+----+------------+----------+

table 2

  ID   Percentage  OrderDate
+----+------------+----------+
  1      45.5      2015-05-08
  1      45.23     2014-05-08 
  1      12.00     2013-05-08
  1      6.45      2012-05-08
  1      18.0      2011-05-08
  1      5.2       2010-05-08
  1      12.0      2009-05-08
  1      22.78     2008-05-08 
  1      48.9      2007-05-08
  1      7.89      2006-05-08
  1      17.96     2005-05-08
  1      11.3      2004-05-08
+----+------------+----------+

Show your query that you have tried? Also, they are called columns. — SS_DBA, Nov 23 '16 at 19:58
don't forget alway accept one answer, the one who helped more! — developer_hatch, Dec 26 '17 at 15:49

score 0 · Answer 1 · edited Nov 24 '16 at 06:24

0

You can use EXCEPT keyword:

SELECT ID, Pourcentage, OrderDate
FROM table1
UNION
(
    SELECT ID, Pourcentage, OrderDate
    FROM table2
    EXCEPT
    SELECT ID, Pourcentage, OrderDate
    FROM table1
)

edited Nov 24 '16 at 06:24

Vikrant

4,920
17
48
72

answered Nov 23 '16 at 20:00

Gareth Lyons

1,942
12
14

could we use except in sql server , i did not know that – Ilyas Nov 23 '16 at 20:01
Yep, introduced with SQL 2008 I believe. – Gareth Lyons Nov 23 '16 at 20:02
i want to make a query which display all the rows from the table 1 and the rows doesnot exist from table 2 – Ilyas Nov 23 '16 at 20:04
`UNION` by itself removes duplicates. – BJones Nov 23 '16 at 20:04
As in insert the missing records to table1? Or just display them? – Gareth Lyons Nov 23 '16 at 20:06
just display them – Ilyas Nov 23 '16 at 20:07
That should do it then. If you want two record sets, just remove the union & the parentheses. Then the first set shows all of table1 and the second result set will show records in table2 that don't exist in table1. – Gareth Lyons Nov 23 '16 at 20:08

score 0 · Answer 2 · edited Nov 23 '16 at 20:21

0

Depending on which columns your are comparing you could use something like below.

SELECT t1.ID, t1.Percentage, t1.OrderDate
FROM table1 t1
UNION ALL
SELECT t2.ID, t2.Percentage, t2.OrderDate
FROM table1 t1
INNER JOIN table2 t2 ON t2.ID <> t1.ID AND t2.Percentage <> t1.Percentage AND t2.OrderDate <> t1.OrderDate

edited Nov 23 '16 at 20:21

answered Nov 23 '16 at 20:05

Alexander Sperring

1
3

score 0 · Answer 3 · edited Nov 23 '16 at 20:25

0

if you want remove duplicate use union

 select ID, Percentage, OrderDate
 from table1
 union
 select ID, Percentage, OrderDate
 from table2

if you want all the rows from both the table use union all

 select ID, Percentage, OrderDate
 from table1
 union all
 select ID, Percentage, OrderDate
 from table2

edited Nov 23 '16 at 20:25

answered Nov 23 '16 at 20:06

ScaisEdge

131,976
10
91
107

Display records from table1 and records from table2 which does not exist in table 1

3 Answers3