What is the difference between INNER JOIN and INTERSECT?

Asked

Viewed 525 times

5

What is the difference between INNER JOIN and INTERSECT? Someone can also illustrate?

sql

  • INNER JOIN is well explained here: What is the difference between INNER JOIN and OUTER JOIN?

  • Note that to make an INTERSECT, as well as UNION or DIFFERENCE (or EXCEPT), of two queries such queries must union-compatible and the result is a set of lines/records with the same attributes of the queries involved.

1 answer

1

Operations with INTERSECT and EXCEPT are performed based on the implicit conversion of the result between two queries.

Operations with INNER JOIN perform a link between two tables or queries through one or more fields.

Syntax

 <query_specification> | ( <query_expression> )    
 EXCEPT | INTERSECT 
 <query_specification> | ( <query_expression> )

EXAMPLE

-- Uses AdventureWorks  

SELECT ProductID   
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder ;  
--Resultado: 238 linhas (produtos que tem WorkOrder)

Follow the reference of the Microsoft documentation. EXCEPT and INTERSECT

  • then I make the following query select ProductID from FROM Production.Product a where exists (select null from FROM Production.WorkOrder where b and a.ProductID = b.ProductID) would be equivalent to the example of his reply? and in the case of EXCEPT could use a not exists?

  • In this case it would be equivalent yes. But it has an adjustment in the where, I think you typed the And by mistake and the b would be the alias of Workorder. In your example, you could still include other columns in select that would have no problem. In my example with INTERSECT, to include a column in a select, would have to include in the other as well.

  • It was a typo anyway.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.