How to get all data from 1st table and corresponding data from 2nd table?

Question

Hello, I have two tables. I need all data from 1st table and one column from 2nd table but putt null if there is no data in 2nd table. What I do

SELECT O.*, E.EXPLANATION
FROM OBIS_CODE O INNER JOIN EXPLANATION E ON O.OBIS_CODE = E.OBIS_CODE

This return me the data if there is data in 2nd table but I need null if there is no data. Please help.

Answer 1

Try this:

SELECT O.*, E.EXPLANATION
FROM OBIS_CODE O LEFT OUTER JOIN EXPLANATION E ON E.OBIS_CODE = O.OBIS_CODE;

Answer 2

@Salman You are correct that ISNULL will work in this example. However, it's not advised for all occasions. Checkout the link in my previous response and go to the section titled "Comparing COALESCE and ISNULL" for potential issues that might eventually cause problems for other uses in the future.

Answer 3

Here is an excellent article with a breakdown of scenerios where ISNULL can be problematic with unexpected behavior:

- http://www.itprotoday.com/software-development/coalesce-vs-isnull

Answer 4

Thanks Ipang. It works fine. Just one thing can I change the text Null to Not Available?

Answer 5

What is the DBMS please..?

Answer 6

MS SQL SERVER

Answer 7

Its work with below query

SELECT O.*, ISNULL (E.EXPLANATION, 'Not Available')
FROM OBIS_CODE O LEFT OUTER JOIN EXPLANATION E ON E.OBIS_CODE = O.OBIS_CODE;

Thanks all

Answer 8

@David, thanks for the hand, interesting point that is :)

@Salman Mushtaq, sorry for the delay, something just came up, yes the ISNULL function is designed for the purpose, yet @David's suggestion is also a viable solution so you might give it a try.

Cheers! @All

Answer 9

@David thanks man for such a valuable information. @Ipang thanks dear.

Best of all ! happy coding :)

Answer 10

@Salman You will want to use the COALESCE SQL function in MS SQL Server.

- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

Applying this to Ipang's example:

SELECT O.*,
COALESCE (E.EXPLANATION, 'Not Available') AS EXPLANATION
FROM OBIS_CODE O LEFT OUTER JOIN EXPLANATION E ON E.OBIS_CODE = O.OBIS_CODE;