EXCEPT vs Left Join

[Anonymous]

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?

[Anonymous]

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.

[LivioLanzo · Accepted solution]

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