Difference between "exists", and "in" in SQL

Question

This is my query

select nom, prenom, mail ,trig 
from dbo.reseau
where  nom, prenom, mail, trig in  
(
    select mail, nom, prenom, mail, trig
    from dbo.reseau
    group by mail, nom, prenom, mail, trig
    having count(1) > 1
)

This query don't work they tell me that I have to use exists. I used id, and it doesn't work.

What's your question here? _"this query don't work"_ isn't a question. Are you asking about [the difference between them](https://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql) which makes your Q a dup, or how to solve something else? — Ilyes, Sep 06 '20 at 20:59
You need to explain more about what you are trying to achieve and show some sample data plus expected results. For a start `IN` only works with a single column, not a list of columns. — Dale K, Sep 06 '20 at 21:00
to solve the probleme of my query. Can i have a result with this query ? — Zakaria Badis, Sep 06 '20 at 21:01
1) [edit] any improvements into your question. 2) **Show some sample data**, because duplicates for each column isn't clear in itself. Do you want to show rows where any column is a duplicate? Or all columns are a duplicate? — Dale K, Sep 06 '20 at 21:13
Sorry, closing it with my vote. I suggest you sit down and spend a day formulating a query that is specific. And in the meantime, instead of "they keep telling me" you also sit down and read documentation - the syntax and examples in teh documentation that clearly explain what "in" and "exists" ae for will help. I am not sure where you got the idea that "in" could be used when TSQL clearly states: (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15) - "Is a subquery that has a result set of one column". Yours has 5. — TomTom, Sep 06 '20 at 21:24
And to add to TomToms comment, you've got a history of poor quality questions, I suggest reading through the FAQ again on how to ask a good question e.g. [Tips for asking a good SQL question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). We all want to help you, but we are all strangers to your system/design/requirements, so you have to make the effort to communicate to us your question in a clear, concise manner. And a [mre] is a must. — Dale K, Sep 06 '20 at 21:34

score 1 · Answer 1 · answered Sep 06 '20 at 21:03

Your syntax is not valid. You seem to be looking for tuple equality:

where (nom, prenom, mail, trig) in (
    select mail, nom, mail, trig
    from ...
)

Few databases support this syntax, and SQL Server is not one of them.

It seems like you want to exhibit duplicates. You could use exists, but you would need a primary key column (or set of columns), and it is not obvious which one it would be from looking at the query. Assuming id:

select r.*
from dbo.reseau r
where exists (
    select 1 
    from dbo.reseau r1
    where 
        r1.id <> r.id 
        and r1.nom = r.nom 
        and r1.prenom = r.prenom 
        and r1.mail = r.mail 
        and r1.trig = r.trig
)

I think this would be simpler expressed with a window count:

select *
from (
    select r.*, count(*) over(partition by nom, prenom, mail, trig) cnt
    from dbo.reseau r
) r
where cnt > 1

this query is used to display duplicates for this colomn – Zakaria Badis Sep 06 '20 at 21:06 — Zakaria Badis, Sep 06 '20 at 21:06

score 1 · Accepted Answer · edited Sep 06 '20 at 21:35

EXISTS: Specifies a subquery to test for the existence of rows.

In other words... Does this subquery return any rows?

IN: Determines whether a specified value matches any value in a subquery or a list.

In other words... Hey, get me the the first and last name of employees whose Job Title is 'Design Engineer', 'Tool Designer', 'Marketing Assistant' code snippet from link below.

SELECT p.FirstName, p.LastName, e.JobTitle  
FROM Person.Person AS p  
JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');  
GO

Further reading

MS DOCS EXISTS

MS DOCS IN

Difference between "exists", and "in" in SQL

2 Answers2