Why Is My T-SQL Left Join Not Working

Why is my t-sql left join not working?

The reason is because you are including the right-hand table in the WHERE clause. You should move that to the ON condition of the LEFT JOIN:

Select    P.appId, S.stepId, S.section, P.start
From      #appSteps    S   With (NoLock)
Left Join #appProgress P   On  S.stepId = P.stepId 
                           And P.appId = 101
Where     S.section Is Not Null

The reason it does this is because the WHERE clause is evaluated after the LEFT JOIN, which then filters out your NULL results from the LEFT JOIN.

Including the right-hand table of a LEFT JOIN (or the left-hand table of a RIGHT JOIN) in the WHERE clause effectively transforms the OUTER JOIN into an INNER JOIN.

SQL SERVER - Left Join does not work. Because?

I suspect the problem here is your understanding of how a subquery works. We have your query:

SELECT *
FROM tblPROproduto pr
     LEFT JOIN (SELECT TOP (1)
                       idproduto,
                       valcusto
                FROM tblproestoque
                ORDER BY identrada DESC) tmp ON tmp.idproduto = pr.idproduto
WHERE pr.idproduto = 8183;

We can separate this into 2 different parts:

SELECT TOP (1)
       idproduto,
       valcusto
FROM tblproestoque
ORDER BY identrada DESC;

and then:

SELECT *
FROM tblPROproduto pr
     LEFT JOIN tmp ON tmp.idproduto = pr.idproduto
WHERE pr.idproduto = 8183;

This might explain to you why what you have isn't working. I'm guessing you are assuming that the ON clause on tmp is derived BEFORE the SELECT of the subquery. This isn't the case. The subquery will be derived, and then the ON. Thus the value of tmp will be whatever is rerturned in the query above.

I suspect what you want is:

SELECT *
FROM tblPROproduto pr
     OUTER APPLY (SELECT TOP (1)
                       ca.idproduto,
                       ca.valcusto
                FROM tblproestoque ca
                WHERE ca.idproduto = pr.idproduto
                ORDER BY ca.identrada DESC) tmp
WHERE pr.idproduto = 8183;

Edit: Added some sample data and explanations for the OP, to help their udnerstanding:

USE Sandbox;
GO

CREATE TABLE Product (ID int IDENTITY(1,1),
                      Sku varchar(10),
                      ProductName varchar(25));
CREATE TABLE ProductOrder (ID int IDENTITY(1,1),
                           ProductID int,
                           OrderDate date,
                           NumberOrdered int);

INSERT INTO dbo.Product (Sku,
                         ProductName)
VALUES ('65432462','Lawn Mower'),
       ('98742347','Helicopter'),
       ('89465735','BBQ');
INSERT INTO dbo.ProductOrder (ProductID,
                              OrderDate,
                              NumberOrdered)
VALUES (1,'20180101',7),
       (1,'20180708',19),
       (2,'20180501',12),
       (3,'20180804',27);
GO

SELECT *
FROM dbo.Product;
SELECT *
FROM dbo.ProductOrder;
GO

--Use the example the OP has in their post:
SELECT *
FROM dbo.Product P
     LEFT JOIN (SELECT TOP 1 *
                FROM dbo.ProductOrder
                ORDER BY OrderDate DESC) PO ON PO.ProductID = P.ID
WHERE P.ID = 2;
--This returns NULLs for all the latter columns.
--Why?
--Inspect the subquery:
SELECT TOP 1 *
FROM dbo.ProductOrder
ORDER BY OrderDate DESC;
--Product ID 3? 3 != 2 so the ON clause fails:
--Demonstrate
SELECT *
FROM dbo.Product P
     CROSS JOIN (SELECT TOP 1 * --CROSS JOIN joins all rows (creates a cartesian product)
                 FROM dbo.ProductOrder
                 ORDER BY OrderDate DESC) PO
WHERE P.ID = 2;
--The solution, use OUTER APPLY:
SELECT *
FROM dbo.Product P
     OUTER APPLY (SELECT TOP 1 *
                  FROM dbo.ProductOrder oa
                  WHERE oa.ProductID = P.ID --WHERE clause, this is like your ON
                  ORDER BY oa.OrderDate DESC) PO
WHERE P.ID = 2;
GO

DROP TABLE dbo.ProductOrder;
DROP TABLE dbo.Product;

Left Outer Join Not Working?

You should move the constraints on prescriptions.filldate into the ON condition of the join, and remove it from the where clause:

LEFT OUTER JOIN prescriptions ON prescriber.dea_no = prescriptions.dea_no
                             AND prescriptions.filldate >= '09-01-12'
                             AND prescriptions.filldate <= '09-17-12'

Otherwise, entries for which there are no prescriptions end up with nulls in prescriptions.filldate, and the WHERE clause throws them away.

LEFT JOIN not working if I use WHERE condition

According to the LEFT JOIN properties, if the record doesn't join with another record into the joining table, all fields of the logical table created with the join are NULL.

This means if you search anything on the joined table on NULL field the search automatically excludes the NULL fields and at this point you are using the LEFT JOIN as an INNER JOIN.

To solve this, you need to add OR field IS NULL to the WHERE condition to keep the NULL results. The second query should be:

SELECT dates as date, COUNT(a.id) AS total_visits 
FROM tbl_calendar as c 
LEFT JOIN tbl_affiliates_link_history as a ON c.dates = DATE(a.link_visit_time) 
WHERE c.dates > '2022-02-01' AND c.dates < '2022-02-13' AND 
      (a.affiliate_id='wilson' OR a.affiliate_id IS NULL)
GROUP BY c.dates

SQL LEFT JOIN not working properly

I managed to fix my problem. I'd like to share my result with you:

SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name
FROM unit_type ut
LEFT JOIN transaction t
ON (ut.id = t.unit_type_id AND t.property_id IN (SELECT id FROM property p WHERE
property_zip_id = 1459))   
WHERE department_id = 1
GROUP BY unit_name
ORDER BY ut.id

Instead of using an extra JOIN, i'd tried using a subquery in my ON-clause which gives my next results:

+-----------+-----------+-------------------+------+
| SUM(..)   | COUNT()   | unit_name         | id   |
+-----------+-----------+-------------------+------+
| NULL      | 0         | WV construction   | 1    |
| 40014     | 11        | WV resale         | 2    |
| NULL      | 0         | WV rent           | 3    |
+-----------+-----------+-------------------+------+

I'd like to thank everyone helping me to fix this question.

Left Join not work with GROUP BY and WHERE clause

I wish other SQL Server questions were so clear - a minimal script that reproduces the problem, something others can easily copy and test, and a clear question, with an easy answer.

A LEFT JOIN would return a NULL for t2.Year which will be eliminated by the WHERE t2.Year=2011 clause.

If you executed just :

select t1.[Text],t2.Year
from @Tbl1 t1 left join @Tbl2 t2 
    on t1.Value = t2.[Value] and t2.Year=2011

You'd get

Text    Year
Val 1   2011
Val 1   2011
Val 1   2011
Val 2   2011
Val 3   2011
Val 4   NULL

and WHERE t2.Year=2011 would eliminate the last row.

To avoid this, you can either allow null years, or move filtering somewhere else.

IS NULL

You could allow nulls in the WHERE clause with IS NULL:

select t1.[Text],count(*)
from @Tbl1 t1 left join @Tbl2 t2 
    on t1.Value = t2.[Value] and t2.Year=2011
where t2.Year=2011 or t2.year is null
group by t1.[Text]
order by t1.Text

This produces:

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   1

That's what the desired response is but I'm not sure val 4 should produce 1 as there are no related rows. Count(id) would produce 0:

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   0

Subquery

You can use a subquery that produces only the T2 appropriate T2 rows, eg:

select t1.[Text], count(*) as 'Count'
from @Tbl1 t1 left join 
    ( select Value 
      from @Tbl2 
      where Year=2011) t2 
    on t1.Value = t2.[Value] 
group by t1.[Text]
order by t1.Text

Cross Apply

Another option would be to use CROSS APPLY to calculate the count of matching T2 rows:

select t1.[Text], Cnt as Count
from @Tbl1 t1 cross apply 
    ( select count(*) as Cnt
      from @Tbl2 t2
      where Year=2011 and t1.Value = t2.[Value] ) t2
order by t1.text

This returns :

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   0

The CROSS APPLY function calculates COUNT(*) for the T2 rows that have a Value equal to t1.Value and returns just that count.

---

Related Topics