-1

I have 2 tabels A and B. These 2 tables have 1 coloumn in common : A.A1 and B.B1. Table A has 800 rows and table B has 500 rows. Clearly, some rows in table A dont have any matching rows in table B. Now, i want to see "what rows in A that do not match row in B" by issuing this query : 

select * from A 
LEFT JOIN B on A.A1 = B.B1 
where date(b.trt_date) = '2017-11-18' and a.tcs_type = 'SALES' 
order by a.tcs_no asc

This query gives out result the same as INNER JOIN. Why ?

When I put this query into a VIEW, it gives out the expected result

This should be dead simple. I am just confused.

I am using : mysql, navicat, sqlyog

KARTHIKEYAN.A
  • 18,210
  • 6
  • 124
  • 133
padjee
  • 125
  • 2
  • 12
  • 2
    You are referencing table B in the where clause! date(b.trt_date) = xxx will remove all rows where there are no matching rows in B. You probably meant to include that predicate in the join clause. – Lord Peter Jan 03 '18 at 09:23
  • ok .. yes, finally i found the answer. thanks...you are right – padjee Jan 03 '18 at 09:38
  • 1
    Incidentally, date() cannot use an index. If you can write that as `b.trt_date BETWEEN '2017-11-18 00:00:00' AND '2017-11-18 23:59:59'`, then it can use an index. – Strawberry Jan 03 '18 at 09:49
  • Find out what left join does. Find out how to express it clearly & concisely. Putting things in quotes does not clarify. Clarifying clarifies. – philipxy Jan 03 '18 at 10:02
  • Possible duplicate of [left join turns into inner join](https://stackoverflow.com/questions/3256304/left-join-turns-into-inner-join) – philipxy Jan 06 '18 at 02:06

2 Answers2

2

Your query:

select * from A 
LEFT JOIN B on A.A1 = B.B1 
where date(b.trt_date) = '2017-11-18' and a.tcs_type = 'SALES' 
order by a.tcs_no asc

Works simillary as an INNER JOIN because you put this WHERE condition:

date(b.trt_date) = '2017-11-18'

You cut, in this way, all rows where b.trt_date is null.

How the condition encapsuled in an ON clause of LEFT JOIN works:

  • If the condition is not satified for main table -> Doens't show the row
  • If the condition is OK for main table but not for the secondary table -> Show the row with all fields of secondary table as NULL
  • If it's OK for both main and the secondary table -> show the row with all information
Oliver Maksimovic
  • 3,204
  • 3
  • 28
  • 44
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0
SELECT * from A 
LEFT JOIN B on A.A1 = B.B1 
WHERE B.A1 IS NULL

Similar post here How to retrieve non-matching results in mysql

Raju
  • 160
  • 2
  • 10