COUNT on LEFT JOIN

Mar 022011

When using COUNT with LEFT JOINs, it is worth noting that is that it does not count NULL values if counting over something different than *.
This is useful if you wish to count how many of the rows fulfill some sort of join clause.

Take the following example.
I’ve created two tables consisting of a unique identifier and a text column. I’ve added test data and for every two row I placed into Tabel_1, I placed one row into Table_2. Then I made a left join query which looks like:

SELECT *
FROM Table_1 AS T
LEFT JOIN Table_2 AS T2 ON T.PK = T2.FK

Which provide an output like this:

LEFT JOIN example for using COUNT | EXEC(@sql)

LEFT JOIN example for using COUNT

Now to illustrate the difference in count, run a query like this:

SELECT COUNT(*) AS AllRows, COUNT(PK) AS RowsFromLeft, COUNT(FK) AS RowsFromRight
FROM Table_1 AS T
LEFT JOIN Table_2 AS T2 ON T.PK = T2.FK

Which provide the following output:

COUNT result for LEFT JOIN | EXEC(@sql)

COUNT result for LEFT JOIN

I’ve seen many more or less creative solutions in my time to how to count over the Right table in a Left Join, because few people seem to read the documentation on something as simple as a COUNT.
However often it is well worth it just to take a peek.

Delete using join-queries

Apr 242008

Occasionally I run into having to clean or modify data using a range of more or less complex conditions.
Then the following syntax comes in handy, because it is actually possible to use complex queries utilizing joins, to delete information from.

The example is realtive simple but it illustrates the point quite well ……. I hope :)

DELETE T1

FROM Table1 T1

INNER JOIN Table2 T2 ON T1.TableOneID = T2.TableOneID

As can be seen the syntax is a little bit different from a normal delete. This query will delete all rows in T1 aka Table1 which would be selected out by my query.

A good rule when building such queries to delete from is to build them up as selects, and then when you have selected the correct data out, you can substitute the SELECT with a DELETE <ALIAS>

I use this type of synax a lot in at work and just wanted to share it, in case it wasn’t as known for people. It can quickly become a powerful tool for deleting the correct data when normal subqueries aren’t useful.

SQL: Watch those join clauses

Nov 122007

Watch those join clauses.

One of the statements I’ve heard a couple of times over the last couple of years, is that it doesn’t matter where you place your additional on-clauses when joining, meaning either in “on” or in “where”. Well it does, depending on what you want to extract of information from your database. Performance wise and result wise.

An easy way to show this is to take two tables.
Suppose I have table (“TableOne”) consisting of a TableOnePK (primary key) and a value, and a table two “TableTwo” consisting of TableOneFK (foreign key) and a value.

If I then do a left join like:

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK AND T2.TableTwoValue = <target value>

This will get you all the rows from TableOne with nulls for the left join where it didn’t match anything in TableTwo. Pretty much as you would expect.

Now change the statement to

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK
WHERE T2.TableTwoValue = <target value>

And now you’ll filter TableTwo prior to the left join and then left join that result into your TableOne, meaning you’ll get a vastly different result then the first one.

Thus one need to consider such things when doing joins as if not careful, it is possible to get a vastly different join result then otherwise intended.