CODEWITHSUNDEEP
sql
Gulshan
Gulshan

Reputation: 131

Display null in the column where condition doesn't satisfy

I have two tables

TRAINING_EMPLOYEE

enter image description here

TRAINING_DEPARTMENT

enter image description here

From both the tables, i want all the columns except DEPARTMENT_ID from the TRAINING_EMPLOYEE and column DEPARTMENT_NAME from the TRAINING_DEPARTMENT

I am doing this

SELECT TE.EMPLOYEE_ID,TE.FIRST_NAME,TE.LAST_NAME,TO_CHAR(HIRE_DATE ,'DD-
MON-YYYY')HIRE_DATE,TD.DEPARTMENT_NAME FROM training_employee TE,
training_department TD  WHERE TE.DEPARTMENT_ID=TD.DEPARTMENT_ID;

And i am getting this enter image description here

But i do want all the 4 rows of TRAINING_EMPLOYEE and null on the Department_name if "TRAINING_EMPLOYEE.DEPARTMENT_ID<>TRAINING_DEPARTMENT.DEPARTMENT_ID"

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You want a left join. And, it is hard because you are using archaic join syntax that doesn't really support it. Here is a simple rule going forward: never use a comma in the from clause. Always use explicit join syntax:

SELECT TE.EMPLOYEE_ID, TE.FIRST_NAME, TE.LAST_NAME,
       TO_CHAR(HIRE_DATE, 'DD-MON-YYYY') as HIRE_DATE, TD.DEPARTMENT_NAME
FROM training_employee TE LEFT JOIN
     training_department TD 
     ON TE.DEPARTMENT_ID = TD.DEPARTMENT_ID;

Upvotes: 2

Related Questions