How to show rows with empty values?

Question

I am trying to figure out how to show rows with empty values with the "JobTitleID" Column.

SQL FILE:

DROP TABLE Employee;
DROP TABLE JobTitle;


-- create our table
CREATE TABLE JobTitle
(
JobTitleID Number(38) PRIMARY KEY,
JobTitle VARCHAR2(25)
)
;


CREATE TABLE Employee
(
EmployeeID Number(38) PRIMARY KEY,
EmployeeFName VARCHAR2(25),
EmployeeLName VARCHAR2(25),
ClientName VARCHAR2(25),
HoursWorked Number(5,2),
ChargeRate Number(5,2),
JobTitleID Number(38),
CONSTRAINT fkJobTitle FOREIGN KEY (JobTitleID) REFERENCES JobTitle(JobTitleID)
)
;



-- populate tables

INSERT ALL 
INTO JobTitle (JobTitleID, JobTitle) VALUES (1,'Project Manager')
INTO JobTitle (JobTitleID, JobTitle) VALUES (2,'Programmer')
INTO JobTitle (JobTitleID, JobTitle) VALUES (3,'Network Specialist')
INTO JobTitle (JobTitleID, JobTitle) VALUES (4,'Technical Support')
INTO JobTitle (JobTitleID, JobTitle) VALUES (5,'Cable Installer')
INTO JobTitle (JobTitleID, JobTitle) VALUES (6,'DBA')
INTO JobTitle (JobTitleID, JobTitle) VALUES (7,'Telecom Engineer')
SELECT * FROM dual;

INSERT ALL 
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (1,'Bob','Smith','Acme Corp.',125.5,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(2,'Bob','Smith','Astro Electric',32,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(3,'Jane','Doe','Acme Corp.',160,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(4,'Jane','Doe','Astro Electric.',220,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (5,'Jane','Doe','Durham Tiles',12,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (6,'Henry','Jones','Lighting Unlimited',245,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(7,'Jane','Fonda','Whale Mart',275,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(8,'Bill','Murray','Whale Mart',145,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(9,'Bill','Murray','ABC Logistics',45,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (10,'John','Jameson','Whale Mart',160,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (11,'John','Jameson','ABC Logistics',130,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(12,'John','Jameson','ABM Systems',8,20,5)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(13,'Homer','Simpson','Flitter',345,25,2)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(14,'John','Carpenter','Speedy Messengers',25,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (15,'John','Carpenter','Flitter',123,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (16,'John','Carpenter','ABM Systems',67,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(17,'John','Carpenter','ABC Logistics',23,15,4)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(18,'Mary','Jane','ABM Systems',43,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES(19,'Mary','Jane','Flitter',156,72.25,1)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (20,'Frank','Herbert','Acme Corp.',55,45,3)
INTO Employee (EmployeeID, EmployeeFName,EmployeeLName,ClientName,HoursWorked,ChargeRate,JobTitleID) VALUES (21,'Kathy','Smith','',0,45,3)
SELECT * FROM dual;



COMMIT;

So far I have this:

COLUMN Employee FORMAT a25;
SELECT JOBTITLE.JOBTITLE AS "Job Title"
FROM EMPLOYEE
FULL JOIN JOBTITLE
ON EMPLOYEE.JOBTITLEID = JOBTITLE.JOBTITLEID
WHERE EMPLOYEE.JOBTITLEID = 0
ORDER BY "Job Title"; /*Ordering it by alphabetical order/*

Now I know that WHERE EMPLOYEE.JOBTITLEID = 0 won't work because of the table JobTitle Not having 0 as a Valid JobTitleID.

The ouput that I am seeking is:

Job Title
-----------------------
DBA
Telecom Engineer

Because of those rows not having any "Employees".

I am currently getting no rows shown.

Answer 1

you just need to check the table design if the field you are looking for with no value is non-numeric and allows NULL then you can use " IS NULL" in the where clause otherwise if its a numeric you can use " = '0'"

Answer 2

you just change

EMPLOYEE.JOBTITLEID = 0

to

EMPLOYEE.JOBTITLEID is null

because in the join condition the value of the column in the join table which is not found will be automatically null example

students
+-------------+--------------+---------
| id          | name         | section|
+-------------+--------------+---------
|           1 | Stud 1       |    1   | 
|           2 | Stud 2       |    3   |
+-------------+--------------+---------

sections

+-------------+--------------+----
| id          | name | teacher   |
+-------------+--------------+----
|           1 | A    | Teacher 1 |
|           2 | B    | Teacher 2 |
+-------------+--------------+----

Select students.id, students.name, sections.name from students
join sections on students.section = sections.id

RESULT

+-------------+--------------+---------
| id          | name         | section|
+-------------+--------------+---------
|           1 | Stud 1 |     |    A   | 
|           2 | Stud 2 |     |   null |
+-------------+--------------+---------

as you see the result of not found section in the join is null

Answer 3

You can use a left join on the jobtitle table.

SELECT DISTINCT JOBTITLE.JOBTITLE AS "Job Title"
FROM JOBTITLE 
LEFT JOIN EMPLOYEE
ON EMPLOYEE.JOBTITLEID = JOBTITLE.JOBTITLEID
WHERE EMPLOYEE.JOBTITLEID IS NULL
ORDER BY "Job Title";

or using not exists.

select jobtitle 
from jobtitle j
where not exists (select 1 from employee where jobtitleid = j.jobtitleid)
order by 1