cancel
Showing results for
Did you mean:
Highlighted
Member

## 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

## 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

Proud to be a Datanaut!

2 REPLIES 2
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

Proud to be a Datanaut!

Member