Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL: How to Select Values in Value List that are NOT IN the Table?

I have a list of e-mail addresses, some of them are in my table, some of them are not. I want to select all e-mails from that list and whether they are in the table or not.

I can get users whose mail adresses are in the table like this:
SELECT u.* FROM USERS u WHERE u.EMAIL IN ('email1', 'email2', 'email3')

But how can I select values in that list which are not exist in the table?

Moreover, how can I select like this:

E-Mail | Status email1 | Exist   email2 | Exist   email3 | Not Exist   email4 | Exist   

Thanks in advance.

like image 642
kubilay Avatar asked Feb 02 '12 13:02

kubilay


People also ask

How can I get data that is not present in another table?

How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

How do you SELECT all records from one table that do not exist in another table Excel?

select [ selecting columns] From table1 Right OUTER JOIN table2 ON(table1. SQL> select e. select [ selecting columns] From table1 Right OUTER JOIN table2 ON(table1. select column_name from table 1 full outer join table 2 on(connection); here all the data from table 1 and table 2 will get retrieved.

How do I SELECT only certain values in a column in SQL?

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a “*” to select all columns. The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

Can we use SELECT without from?

Let's get that straight from the beginning: select without from is not standard conforming SQL. Full stop. Nevertheless it works in many databases—also in standard conforming ones.


1 Answers

For SQL Server 2008

SELECT email,        CASE          WHEN EXISTS(SELECT *                      FROM   Users U                      WHERE  E.email = U.email) THEN 'Exist'          ELSE 'Not Exist'        END AS [Status] FROM   (VALUES('email1'),               ('email2'),               ('email3'),               ('email4')) E(email)   

For previous versions you can do something similar with a derived table UNION ALL-ing the constants.

/*The SELECT list is the same as previously*/ FROM ( SELECT 'email1' UNION ALL SELECT 'email2' UNION ALL SELECT 'email3' UNION ALL SELECT 'email4' )  E(email) 

Or if you want just the non-existing ones (as implied by the title) rather than the exact resultset given in the question, you can simply do this

SELECT email FROM   (VALUES('email1'),               ('email2'),               ('email3'),               ('email4')) E(email)   EXCEPT SELECT email FROM Users 
like image 70
Martin Smith Avatar answered Oct 09 '22 03:10

Martin Smith