Multi tool use

Left joined 2 tables, result looks like inner join

Question may be easy, but I'm quite new to SQL so here goes.

What I'm trying to accomplish:

I have TableA and TableB. TableA contains most of my data, however it should modify one of the columns (modified result) using data from TableB if there's a match on ID and date range.

TableA

TableB

TableA

TableB

Here's my query:

SELECT
t1.ID, t1.a, t1.b, t1.c, t1.d, t1.e, t1.f, t1.g, t1.h, t1.i,
(t1.totalcost - (ISNULL(t2.cost, 0) * ISNULL(t2.qty,0))) AS modified_result,
t1.j, t1.k, t1.createdate
FROM
TableA as t1
LEFT JOIN
TableB AS t2 ON t1.ID = t2.ID
WHERE
t1.createdate BETWEEN '2018-06-19' AND '2018-06-23'
AND t2.createdate BETWEEN '2018-06-19' AND '2018-06-23'
AND t2.trans_type = 'H'
ORDER BY
t1.createdate


There's like 2000+ records in TableA satisfying those criteria for TableA and only a few matching records from TableB. Running this query produces equal row number to TableB result, which for me looks like an inner join.

TableA

TableA

TableB

TableB

I probably have it wrong in my WHERE section, but I can't really see it.

WHERE

4 Answers
4

I'm not sure about technical but I'm answer you from my experience from my work

when you filter t2 in WHERE clause. DBMS filter whole result after JOIN

WHERE

JOIN

if you want to keep all rows of t1 using subQuery instead t2 or move filter t2 to join condition example

use Subquery instead T2

SELECT t1.ID, t1.a, t1.b, t1.c, t1.d, t1.e, t1.f, t1.g, t1.h, t1.i,
(t1.totalcost - (ISNULL(t2.cost, 0) * ISNULL(t2.qty,0))) as modified_result,
t1.j, t1.k, t1.createdate
FROM TableA as t1
LEFT JOIN (
SELECT *
FROM TableB t2
WHERE t2.createdate between '2018-06-19' and '2018-06-23' and t2.trans_type = 'H'
) AS t2 ON t1.ID = t2.ID
WHERE t1.createdate between '2018-06-19' and '2018-06-23'
ORDER BY t1.createdate


or

move filter t2 to join condition

SELECT t1.ID, t1.a, t1.b, t1.c, t1.d, t1.e, t1.f, t1.g, t1.h, t1.i,
(t1.totalcost - (ISNULL(t2.cost, 0) * ISNULL(t2.qty,0))) as modified_result,
t1.j, t1.k, t1.createdate
FROM TableA as t1
LEFT JOIN TableB AS t2 ON t1.ID = t2.ID and t2.createdate between '2018-06-19' and '2018-06-23' and t2.trans_type = 'H'
WHERE t1.createdate between '2018-06-19' and '2018-06-23'
ORDER BY t1.createdate


above code I'm didn't test

Hope it's help.

SELECT
t1.ID, t1.a, t1.b, t1.c, t1.d, t1.e, t1.f, t1.g, t1.h, t1.i,
(t1.totalcost - (ISNULL(t2.cost, 0) * ISNULL(t2.qty,0))) AS modified_result,
t1.j, t1.k, t1.createdate
FROM
TableA as t1
LEFT JOIN
TableB AS t2 ON t1.ID = t2.ID AND t2.createdate BETWEEN '2018-06-19' AND '2018-06-23'
AND t2.trans_type = 'H'
WHERE
t1.createdate BETWEEN '2018-06-19' AND '2018-06-23'

ORDER BY
t1.createdate


here is your where condition

WHERE t1.createdate between '2018-06-19' and '2018-06-23' and t2.createdate
between '2018-06-19' and '2018-06-23' ***and t2.trans_type = 'H'***


here your rows will be filtered as per trans type... Basically it is limiting the rows

Including criteria from a LEFT OUTER JOIN table in the WHERE clause does NOT magically convert it into an INNER JOIN; there's no "special rule" that you have to include all criteria in the ON clause.

LEFT OUTER JOIN

WHERE

INNER JOIN

ON

What has happened here is that your query without the WHERE clause will return some NULL values from t2 and you then exclude these by including criteria for this alias that doesn't include NULL values.

WHERE

NULL

t2

NULL

If you were to rewrite your query like this then it would work as you want it to:

SELECT t1.ID, t1.a, t1.b, t1.c, t1.d, t1.e, t1.f, t1.g, t1.h, t1.i,
(t1.totalcost - (ISNULL(t2.cost, 0) * ISNULL(t2.qty,0))) as modified_result,
t1.j, t1.k, t1.createdate
FROM TableA as t1
LEFT JOIN TableB AS t2 ON t1.ID = t2.ID
WHERE t1.createdate between '2018-06-19' and '2018-06-23' and (t2.createdate IS NULL OR t2.createdate
between '2018-06-19' and '2018-06-23') and (t2.trans_type IS NULL OR t2.trans_type = 'H')
ORDER BY t1.createdate


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.