How to get different rows from two tables in SQL Server

Question

I'm trying to build a query that can give me the different rows between two tables.

Here is my code:

try
{
    string searchquery = "SELECT* FROM NewList EXCEPT SELECT* FROM Restaurants UNION All";

    SqlCommand searchcom = new SqlCommand(searchquery, connection);
    SqlDataAdapter da = new SqlDataAdapter(searchcom);
    DataSet ds = new DataSet();

    connection.Open();
    da.Fill(ds);

    dataGridView1.DataSource = ds.Tables[0];
    connection.Close();
}
catch (Exception er)
{
    Debug.WriteLine(er.Message);
    return;
}

I get the following error:

Incorrect syntax near 'All'.

What is the correct way/syntax to use Union All in SqlCommand class? I've tried to put it it my query string in too many ways but it's giving me the same error every time. How can I implement it in my search query with correct syntax ?

"What is the correct way/syntax to use Union All ?" isn't it? you just wrote invalid SQL query, and in the example, it shows how to write a valid query using UNION ALL — styx, Dec 31 '19 at 07:41
What is the correct way/syntax to use Union All in SqlCommand query string. That's the question. — Bora, Dec 31 '19 at 07:46
Are you trying to find the rows in one table that are not present in the other? — AsheraH, Dec 31 '19 at 07:54
@AsheraH yes. There are nearly 26000 rows in "NewList" and 25000 rows in "Restaurants". I'm trying to get that new 1000 row to a datagridview — Bora, Dec 31 '19 at 07:58

score 2 · Accepted Answer · edited Dec 31 '19 at 09:17

2

Your query is incorrect from UNION ALL on.

You can try it like this:

string searchquery = "SELECT * FROM NewList UNION ALL SELECT * FROM Restaurants ";

However, you need to be aware that:

It does not remove duplicate rows between the various SELECT statements (all rows are returned)
Each SELECT statement within the UNION ALL must have the same number of columns in the result sets with similar data types

Read here for more details.

Updated

You should use EXCEPT instead of UNION ALL if you want to get rows in one table that are not present in the other one.

edited Dec 31 '19 at 09:17

marc_s

732,580
175
1,330
1,459

answered Dec 31 '19 at 07:46

Nguyễn Văn Phong

13,506
17
39
56

Thank you. There are nearly 26000 rows in "NewList" and 25000 rows in "Restaurants". I'm trying to get that new 1000 row to a datagridview. This doesn't give me that result – Bora Dec 31 '19 at 07:59
I don't think the **The number of fields** of 2 table are the same. BTW, give us your sample data of 2 table and your expectation result value. Then we can give you advise. – Nguyễn Văn Phong Dec 31 '19 at 08:04
It seems that you should you `Except` instead of `Union All` – Nguyễn Văn Phong Dec 31 '19 at 08:06
Number of fields, column names, data types and datas are all the same in two tables. And except doesn't work either. – Bora Dec 31 '19 at 08:09
Did you try on run my query in sql server? – Nguyễn Văn Phong Dec 31 '19 at 08:12
1

SELECT * FROM NewList EXCEPT SELECT * FROM Restaurants – Nguyễn Văn Phong Dec 31 '19 at 08:12

score 0 · Answer 2 · answered Dec 31 '19 at 09:07

0

Select Checking.RestID, FROM 
(SELECT RestID FROM NewList EXCEPT Select RestID from Restaurants) as Checking
LEFT JOIN NewList ON Checking.RestID = NewList.RestID

this worked, thanks to anyone !

answered Dec 31 '19 at 09:07

Bora

39
6

Yes, As i said before, you should use `Except` instead of `UNION ALL`. BTW, Don't forget give me an upvote. I really appreciate that. @Bora – Nguyễn Văn Phong Dec 31 '19 at 09:11

How to get different rows from two tables in SQL Server

2 Answers2