Menu
I want to join two tables, with the number of records for each type being counted. If there are no records of that type in the left table I want a 0 to be returned, not a null.
How can I do this?
270
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
For Oracle you can use: NVL(columnName,deafultValue) :- NVL is used to convert a null value to a default value in the query output. eg. If you want to replace null values with 'NA' then use something like this.
The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.
Null values in tables or views being joined never match each other. Since bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column in the inner table. The result of a join of null with any other value is null.
Use:
ISNULL(count(*), 0) You can use "CASE"
SELECT T1.NAME, CASE WHEN T2.DATA IS NULL THEN 0 ELSE T2.DATA END FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
