Outer join to find if records exist

Transact-SQL
1

Hello,
I have these two tables: Invoices and InvoiceLines, linked together by InvoiceId field.

Is there some Left Outer join just to check if there exist any InvoiceLines for the Invoices? (true or false);
something like If Exists(Select...)
For now I've tried (for a 2million record Invoice table and roughly 40million lines table):
1. Select i.InvoiceNumber,Case When l.InvoiceIs Is null Then 0 Else 1 End As HasLines From Invoices i Left Outer Join (Select InvoiceId From InvoiceLines Group By InvoiceId) l on i.InvoiceId=l.InvoiceId (the quickest 1'13")
2. Select i.InvoiceNumber,Case When l.InvoiceIs Is null Then 0 Else 1 End As HasLines From Invoices i Left Outer Join (Select Distinct InvoiceId From InvoiceLines) l on i.InvoiceId=l.InvoiceId (1'15")
3. Select i.InvoiceNumber,Case When l.InvoiceIs Is null Then 0 Else 1 End As HasLines From Invoices i Left Outer Join (Select InvoiceId From (Select InvoiceId,N=Row_Number() Over (Partition By InvoiceId Order By Id) FromInvoiceLines) l Where N=1) l on i.InvoiceId=l.InvoiceId (1:32")
4. Select i.InvoiceNumber,Case When l.InvoiceIs Is null Then 0 Else 1 End As HasLines From Invoices i Left Outer Join (Select InvoiceId From Invoice d Where Exists(Select InvoiceId From InvoiceLines dl Where dl.InvoiceId=d.InvoiceId)) l on i.InvoiceId=l.InvoiceId (1'20")

But I don't want to actually read something, just to 'touch' the table and see if at least one record exist.
Do you think there is another way?

Thank you, Daniel

2

When you write a stored procedure for example you see a lot of constuctions like this:

IF EXISTS
(SELECT 1
FROM InvoiceLines IL
LEFT OUTER JOIN Invoices I
ON I.InvoiceID=Il.InvoiceID
WHERE I.InvoiceId IS NULL
)
BEGIN
/* Delete invoiceLine */
DELETE ....
END

You can also check if a cascade delete is a better approch.

3 
SELECT  i.InvoiceNumber
    ,   ISNULL(l.HasLines, 0) AS HasLines
FROM    Invoices i
OUTER APPLY 
    (
        SELECT  TOP (1) 
                1 AS HasLines
        FROM    InvoiceLines l
        WHERE   i.InvoiceId = l.InvoiceId
    ) l;

This query would benefit from an index on InvoiceLines (InvoiceId)

If there is an index on InvoiceId, then here's how this query will work:
For each row in Invoices, an index seek will occur to find the first matching row of InvoiceLines.

Without a supporting index, it will probably not perform any better than your other attempts.

4

SqlHippo: I've just tested the query and it's 30 to 40% faster than my best, Great!
I do have an index on InvoiceId

Thank you!

5

Hi,
i had this problem often and the only solution that worked for me is to JOIN the second table with itself as follows:

select
A.InvoiceId ,
case when not exists (select InvoiceIs from InvoiceLines C where C.InvoiceId = B.InvoiceId) THEN 0 ELSE 1 END as HasLines

FROM Invoices A LEFT JOIN InvoiceLines B

ON A.InvoiceId = B.InvoiceId ORDER BY A.InvoiceId

I hope it works for you too.