cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

EXCEPT vs Left Join

Is there a difference between EXCEPT function in DAX and Left Join in SQL? 

Also, is there a difference between INTERSECT function in DAX and Inner Join in SQL?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: EXCEPT vs Left Join

EXCEPT  returns all the rows from Table1 which are not in Table2

 

Table1 = A, B, C, D

Table2 = B,C,E,F,G

Except(Table1,Table2) = A, D

Except Table2, Table1 ) = E, F, G

 

The left join is different because it returns always all rows from Table1 and the matching rows from table 2 in another column

 

Table1 Left Join Table2 = 

Col1 - Col 2

A     - empty

B    - B

C   - C

D  - empty

 

Intersect and Inner Join are more similar because they both returns only the matching rows between table1 and table2

INTERSECT( Table1, Table2 ) = B, C

 

INTERSECT in DAX looks at all the columns to find matches whereas in the INNER JOIN in SQL you can specity at which column to look and the same is true for EXCEP and LEFT JOIN.

 

In Power Query you can also perform these joins easily and specify the columns as you do in SQL. Look at the Table.NestedJoin function in the M reference website

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

2 REPLIES 2
Super User
Super User

Re: EXCEPT vs Left Join

EXCEPT  returns all the rows from Table1 which are not in Table2

 

Table1 = A, B, C, D

Table2 = B,C,E,F,G

Except(Table1,Table2) = A, D

Except Table2, Table1 ) = E, F, G

 

The left join is different because it returns always all rows from Table1 and the matching rows from table 2 in another column

 

Table1 Left Join Table2 = 

Col1 - Col 2

A     - empty

B    - B

C   - C

D  - empty

 

Intersect and Inner Join are more similar because they both returns only the matching rows between table1 and table2

INTERSECT( Table1, Table2 ) = B, C

 

INTERSECT in DAX looks at all the columns to find matches whereas in the INNER JOIN in SQL you can specity at which column to look and the same is true for EXCEP and LEFT JOIN.

 

In Power Query you can also perform these joins easily and specify the columns as you do in SQL. Look at the Table.NestedJoin function in the M reference website

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: EXCEPT vs Left Join

Answer to Q#1: No
Explanation: EXCEPT returns the rows of the left side table, which do not appear on the right side table.

EXCEPT takes in two tables as arguements i.e. =EXCEPT(table1,table2). It's output is a table which has all the records from table1 which don't appear in table 2. If there is a matching record, it ignores it. Ex: If Table1 has A,B and C and Table2 has B, D and F, the output table will have only A and C. A left join in SQL would have included B as well. Furthermore, unlike SQL, EXCEPT matches entire records or rows, not columns. For this purpose, table1 and table2 need to have equal number of columns.

Answer to Q#2: Sort of Yes.
Explanation: INTERSECT returns the rows from table1 which appear in table2.

 

In the above example, it would only return B.

Again, important to remember is INTERSECT matches entire records, not columns. Hence, it's almost inner join but not completely.