Getting vlaues only from Left Table using left join-CodePudding

suppose I have two table, Table A and Table B. Using left join how can I get value of only Table A i.e. it should not contains common record from Table B.

Select * from 
        Table A left join Table B
                    on A.id=B.id

Can anyone pls help?

CodePudding user response：

You can only select columns by prefix A.*.

Select A.* from 
        Table A left join Table B
                    on A.id=B.id

CodePudding user response：

In a left join, if the right table has no matching data then the column values for those right table rows will be null; you can check that this way:

Select a.* 
from 
    TableA A 
    left join 
    TableB B
    on A.id=B.id
where B.id is null

This will return all rows in A where there is no matching record in B. Note that if B.id is not unique the records returned for A can be duplicated (because of the join). I noticed that your example is a self join (same table is used both as LEFT and RIGHT tables, and the join is via Id). If this is the case this query is meaningless because it will return nothing (because all rows are common). Maybe you can tell more about your problem if this is indeed a self-join.