INNER JOIN with CASE Statement

View All Scripts Login to Run Script

Script Name INNER JOIN with CASE Statement
Description INNER JOIN with CASE Statement
Area SQL General / SQL Query
Contributor Ijhyyy
Created Thursday June 21, 2018

Statement 1

SELECT 
    employee_id as EmployeeID, 
    first_name as FirstName,  
    last_name as LastName, 
    hire_date as HireDate, 
    t2.job_title as JobTitle, 
CASE 
    WHEN hire_date <= '31-DEC-03' THEN 'Old Employee' 
    ELSE 'New Employee' 
END AS EmployeeStatus 
FROM hr.employees t1 
INNER JOIN hr.jobs t2 ON t1.job_id = t2.job_id;


EMPLOYEEID FIRSTNAME LASTNAME HIREDATE JOBTITLE EMPLOYEESTATUS
206 William Gietz 07-JUN-02 Public Accountant Old Employee
205 Shelley Higgins 07-JUN-02 Accounting Manager Old Employee
200 Jennifer Whalen 17-SEP-03 Administration Assistant Old Employee
100 Steven King 17-JUN-03 President Old Employee
102 Lex De Haan 13-JAN-01 Administration Vice President Old Employee
101 Neena Kochhar 21-SEP-05 Administration Vice President New Employee
110 John Chen 28-SEP-05 Accountant New Employee
109 Daniel Faviet 16-AUG-02 Accountant Old Employee
113 Luis Popp 07-DEC-07 Accountant New Employee
111 Ismael Sciarra 30-SEP-05 Accountant New Employee
112 Jose Manuel Urman 07-MAR-06 Accountant New Employee
108 Nancy Greenberg 17-AUG-02 Finance Manager Old Employee
203 Susan Mavris 07-JUN-02 Human Resources Representative Old Employee
103 Alexander Hunold 03-JAN-06 Programmer New Employee
104 Bruce Ernst 21-MAY-07 Programmer New Employee
107 Diana Lorentz 07-FEB-07 Programmer New Employee
106 Valli Pataballa 05-FEB-06 Programmer New Employee
105 David Austin 25-JUN-05 Programmer New Employee
201 Michael Hartstein 17-FEB-04 Marketing Manager New Employee
202 Pat Fay 17-AUG-05 Marketing Representative New Employee
204 Hermann Baer 07-JUN-02 Public Relations Representative Old Employee
116 Shelli Baida 24-DEC-05 Purchasing Clerk New Employee
115 Alexander Khoo 18-MAY-03 Purchasing Clerk Old Employee
117 Sigal Tobias 24-JUL-05 Purchasing Clerk New Employee
118 Guy Himuro 15-NOV-06 Purchasing Clerk New Employee
119 Karen Colmenares 10-AUG-07 Purchasing Clerk New Employee
114 Den Raphaely 07-DEC-02 Purchasing Manager Old Employee
148 Gerald Cambrault 15-OCT-07 Sales Manager New Employee
147 Alberto Errazuriz 10-MAR-05 Sales Manager New Employee
146 Karen Partners 05-JAN-05 Sales Manager New Employee
145 John Russell 01-OCT-04 Sales Manager New Employee
149 Eleni Zlotkey 29-JAN-08 Sales Manager New Employee
155 Oliver Tuvault 23-NOV-07 Sales Representative New Employee
156 Janette King 30-JAN-04 Sales Representative New Employee
157 Patrick Sully 04-MAR-04 Sales Representative New Employee
158 Allan McEwen 01-AUG-04 Sales Representative New Employee
159 Lindsey Smith 10-MAR-05 Sales Representative New Employee
160 Louise Doran 15-DEC-05 Sales Representative New Employee
161 Sarath Sewall 03-NOV-06 Sales Representative New Employee
179 Charles Johnson 04-JAN-08 Sales Representative New Employee
163 Danielle Greene 19-MAR-07 Sales Representative New Employee
164 Mattea Marvins 24-JAN-08 Sales Representative New Employee
165 David Lee 23-FEB-08 Sales Representative New Employee
166 Sundar Ande 24-MAR-08 Sales Representative New Employee
167 Amit Banda 21-APR-08 Sales Representative New Employee
168 Lisa Ozer 11-MAR-05 Sales Representative New Employee
169 Harrison Bloom 23-MAR-06 Sales Representative New Employee
170 Tayler Fox 24-JAN-06 Sales Representative New Employee
171 William Smith 23-FEB-07 Sales Representative New Employee
172 Elizabeth Bates 24-MAR-07 Sales Representative New Employee
173 Sundita Kumar 21-APR-08 Sales Representative New Employee
174 Ellen Abel 11-MAY-04 Sales Representative New Employee
175 Alyssa Hutton 19-MAR-05 Sales Representative New Employee
176 Jonathon Taylor 24-MAR-06 Sales Representative New Employee
177 Jack Livingston 23-APR-06 Sales Representative New Employee
178 Kimberely Grant 24-MAY-07 Sales Representative New Employee
154 Nanette Cambrault 09-DEC-06 Sales Representative New Employee
153 Christopher Olsen 30-MAR-06 Sales Representative New Employee
152 Peter Hall 20-AUG-05 Sales Representative New Employee
151 David Bernstein 24-MAR-05 Sales Representative New Employee
150 Peter Tucker 30-JAN-05 Sales Representative New Employee
162 Clara Vishney 11-NOV-05 Sales Representative New Employee
184 Nandita Sarchand 27-JAN-04 Shipping Clerk New Employee
185 Alexis Bull 20-FEB-05 Shipping Clerk New Employee
186 Julia Dellinger 24-JUN-06 Shipping Clerk New Employee
187 Anthony Cabrio 07-FEB-07 Shipping Clerk New Employee
188 Kelly Chung 14-JUN-05 Shipping Clerk New Employee
189 Jennifer Dilly 13-AUG-05 Shipping Clerk New Employee
190 Timothy Gates 11-JUL-06 Shipping Clerk New Employee
191 Randall Perkins 19-DEC-07 Shipping Clerk New Employee
192 Sarah Bell 04-FEB-04 Shipping Clerk New Employee
193 Britney Everett 03-MAR-05 Shipping Clerk New Employee
194 Samuel McCain 01-JUL-06 Shipping Clerk New Employee
195 Vance Jones 17-MAR-07 Shipping Clerk New Employee
196 Alana Walsh 24-APR-06 Shipping Clerk New Employee
197 Kevin Feeney 23-MAY-06 Shipping Clerk New Employee
198 Donald OConnell 21-JUN-07 Shipping Clerk New Employee
199 Douglas Grant 13-JAN-08 Shipping Clerk New Employee
180 Winston Taylor 24-JAN-06 Shipping Clerk New Employee
181 Jean Fleaur 23-FEB-06 Shipping Clerk New Employee
182 Martha Sullivan 21-JUN-07 Shipping Clerk New Employee
183 Girard Geoni 03-FEB-08 Shipping Clerk New Employee
132 TJ Olson 10-APR-07 Stock Clerk New Employee
143 Randall Matos 15-MAR-06 Stock Clerk New Employee
142 Curtis Davies 29-JAN-05 Stock Clerk New Employee
141 Trenna Rajs 17-OCT-03 Stock Clerk Old Employee
140 Joshua Patel 06-APR-06 Stock Clerk New Employee
125 Julia Nayer 16-JUL-05 Stock Clerk New Employee
126 Irene Mikkilineni 28-SEP-06 Stock Clerk New Employee
127 James Landry 14-JAN-07 Stock Clerk New Employee
128 Steven Markle 08-MAR-08 Stock Clerk New Employee
129 Laura Bissot 20-AUG-05 Stock Clerk New Employee
130 Mozhe Atkinson 30-OCT-05 Stock Clerk New Employee
131 James Marlow 16-FEB-05 Stock Clerk New Employee
144 Peter Vargas 09-JUL-06 Stock Clerk New Employee
133 Jason Mallin 14-JUN-04 Stock Clerk New Employee
134 Michael Rogers 26-AUG-06 Stock Clerk New Employee
135 Ki Gee 12-DEC-07 Stock Clerk New Employee
136 Hazel Philtanker 06-FEB-08 Stock Clerk New Employee
137 Renske Ladwig 14-JUL-03 Stock Clerk Old Employee
138 Stephen Stiles 26-OCT-05 Stock Clerk New Employee
139 John Seo 12-FEB-06 Stock Clerk New Employee
124 Kevin Mourgos 16-NOV-07 Stock Manager New Employee
123 Shanta Vollman 10-OCT-05 Stock Manager New Employee
122 Payam Kaufling 01-MAY-03 Stock Manager Old Employee
121 Adam Fripp 10-APR-05 Stock Manager New Employee
120 Matthew Weiss 18-JUL-04 Stock Manager New Employee

107 rows selected.

EMPLOYEEID	FIRSTNAME	LASTNAME	HIREDATE	JOBTITLE	EMPLOYEESTATUS
206	William	Gietz	07-JUN-02	Public Accountant	Old Employee
205	Shelley	Higgins	07-JUN-02	Accounting Manager	Old Employee
200	Jennifer	Whalen	17-SEP-03	Administration Assistant	Old Employee
100	Steven	King	17-JUN-03	President	Old Employee
102	Lex	De Haan	13-JAN-01	Administration Vice President	Old Employee
101	Neena	Kochhar	21-SEP-05	Administration Vice President	New Employee
110	John	Chen	28-SEP-05	Accountant	New Employee
109	Daniel	Faviet	16-AUG-02	Accountant	Old Employee
113	Luis	Popp	07-DEC-07	Accountant	New Employee
111	Ismael	Sciarra	30-SEP-05	Accountant	New Employee
112	Jose Manuel	Urman	07-MAR-06	Accountant	New Employee
108	Nancy	Greenberg	17-AUG-02	Finance Manager	Old Employee
203	Susan	Mavris	07-JUN-02	Human Resources Representative	Old Employee
103	Alexander	Hunold	03-JAN-06	Programmer	New Employee
104	Bruce	Ernst	21-MAY-07	Programmer	New Employee
107	Diana	Lorentz	07-FEB-07	Programmer	New Employee
106	Valli	Pataballa	05-FEB-06	Programmer	New Employee
105	David	Austin	25-JUN-05	Programmer	New Employee
201	Michael	Hartstein	17-FEB-04	Marketing Manager	New Employee
202	Pat	Fay	17-AUG-05	Marketing Representative	New Employee
204	Hermann	Baer	07-JUN-02	Public Relations Representative	Old Employee
116	Shelli	Baida	24-DEC-05	Purchasing Clerk	New Employee
115	Alexander	Khoo	18-MAY-03	Purchasing Clerk	Old Employee
117	Sigal	Tobias	24-JUL-05	Purchasing Clerk	New Employee
118	Guy	Himuro	15-NOV-06	Purchasing Clerk	New Employee
119	Karen	Colmenares	10-AUG-07	Purchasing Clerk	New Employee
114	Den	Raphaely	07-DEC-02	Purchasing Manager	Old Employee
148	Gerald	Cambrault	15-OCT-07	Sales Manager	New Employee
147	Alberto	Errazuriz	10-MAR-05	Sales Manager	New Employee
146	Karen	Partners	05-JAN-05	Sales Manager	New Employee
145	John	Russell	01-OCT-04	Sales Manager	New Employee
149	Eleni	Zlotkey	29-JAN-08	Sales Manager	New Employee
155	Oliver	Tuvault	23-NOV-07	Sales Representative	New Employee
156	Janette	King	30-JAN-04	Sales Representative	New Employee
157	Patrick	Sully	04-MAR-04	Sales Representative	New Employee
158	Allan	McEwen	01-AUG-04	Sales Representative	New Employee
159	Lindsey	Smith	10-MAR-05	Sales Representative	New Employee
160	Louise	Doran	15-DEC-05	Sales Representative	New Employee
161	Sarath	Sewall	03-NOV-06	Sales Representative	New Employee
179	Charles	Johnson	04-JAN-08	Sales Representative	New Employee
163	Danielle	Greene	19-MAR-07	Sales Representative	New Employee
164	Mattea	Marvins	24-JAN-08	Sales Representative	New Employee
165	David	Lee	23-FEB-08	Sales Representative	New Employee
166	Sundar	Ande	24-MAR-08	Sales Representative	New Employee
167	Amit	Banda	21-APR-08	Sales Representative	New Employee
168	Lisa	Ozer	11-MAR-05	Sales Representative	New Employee
169	Harrison	Bloom	23-MAR-06	Sales Representative	New Employee
170	Tayler	Fox	24-JAN-06	Sales Representative	New Employee
171	William	Smith	23-FEB-07	Sales Representative	New Employee
172	Elizabeth	Bates	24-MAR-07	Sales Representative	New Employee
173	Sundita	Kumar	21-APR-08	Sales Representative	New Employee
174	Ellen	Abel	11-MAY-04	Sales Representative	New Employee
175	Alyssa	Hutton	19-MAR-05	Sales Representative	New Employee
176	Jonathon	Taylor	24-MAR-06	Sales Representative	New Employee
177	Jack	Livingston	23-APR-06	Sales Representative	New Employee
178	Kimberely	Grant	24-MAY-07	Sales Representative	New Employee
154	Nanette	Cambrault	09-DEC-06	Sales Representative	New Employee
153	Christopher	Olsen	30-MAR-06	Sales Representative	New Employee
152	Peter	Hall	20-AUG-05	Sales Representative	New Employee
151	David	Bernstein	24-MAR-05	Sales Representative	New Employee
150	Peter	Tucker	30-JAN-05	Sales Representative	New Employee
162	Clara	Vishney	11-NOV-05	Sales Representative	New Employee
184	Nandita	Sarchand	27-JAN-04	Shipping Clerk	New Employee
185	Alexis	Bull	20-FEB-05	Shipping Clerk	New Employee
186	Julia	Dellinger	24-JUN-06	Shipping Clerk	New Employee
187	Anthony	Cabrio	07-FEB-07	Shipping Clerk	New Employee
188	Kelly	Chung	14-JUN-05	Shipping Clerk	New Employee
189	Jennifer	Dilly	13-AUG-05	Shipping Clerk	New Employee
190	Timothy	Gates	11-JUL-06	Shipping Clerk	New Employee
191	Randall	Perkins	19-DEC-07	Shipping Clerk	New Employee
192	Sarah	Bell	04-FEB-04	Shipping Clerk	New Employee
193	Britney	Everett	03-MAR-05	Shipping Clerk	New Employee
194	Samuel	McCain	01-JUL-06	Shipping Clerk	New Employee
195	Vance	Jones	17-MAR-07	Shipping Clerk	New Employee
196	Alana	Walsh	24-APR-06	Shipping Clerk	New Employee
197	Kevin	Feeney	23-MAY-06	Shipping Clerk	New Employee
198	Donald	OConnell	21-JUN-07	Shipping Clerk	New Employee
199	Douglas	Grant	13-JAN-08	Shipping Clerk	New Employee
180	Winston	Taylor	24-JAN-06	Shipping Clerk	New Employee
181	Jean	Fleaur	23-FEB-06	Shipping Clerk	New Employee
182	Martha	Sullivan	21-JUN-07	Shipping Clerk	New Employee
183	Girard	Geoni	03-FEB-08	Shipping Clerk	New Employee
132	TJ	Olson	10-APR-07	Stock Clerk	New Employee
143	Randall	Matos	15-MAR-06	Stock Clerk	New Employee
142	Curtis	Davies	29-JAN-05	Stock Clerk	New Employee
141	Trenna	Rajs	17-OCT-03	Stock Clerk	Old Employee
140	Joshua	Patel	06-APR-06	Stock Clerk	New Employee
125	Julia	Nayer	16-JUL-05	Stock Clerk	New Employee
126	Irene	Mikkilineni	28-SEP-06	Stock Clerk	New Employee
127	James	Landry	14-JAN-07	Stock Clerk	New Employee
128	Steven	Markle	08-MAR-08	Stock Clerk	New Employee
129	Laura	Bissot	20-AUG-05	Stock Clerk	New Employee
130	Mozhe	Atkinson	30-OCT-05	Stock Clerk	New Employee
131	James	Marlow	16-FEB-05	Stock Clerk	New Employee
144	Peter	Vargas	09-JUL-06	Stock Clerk	New Employee
133	Jason	Mallin	14-JUN-04	Stock Clerk	New Employee
134	Michael	Rogers	26-AUG-06	Stock Clerk	New Employee
135	Ki	Gee	12-DEC-07	Stock Clerk	New Employee
136	Hazel	Philtanker	06-FEB-08	Stock Clerk	New Employee
137	Renske	Ladwig	14-JUL-03	Stock Clerk	Old Employee
138	Stephen	Stiles	26-OCT-05	Stock Clerk	New Employee
139	John	Seo	12-FEB-06	Stock Clerk	New Employee
124	Kevin	Mourgos	16-NOV-07	Stock Manager	New Employee
123	Shanta	Vollman	10-OCT-05	Stock Manager	New Employee
122	Payam	Kaufling	01-MAY-03	Stock Manager	Old Employee
121	Adam	Fripp	10-APR-05	Stock Manager	New Employee
120	Matthew	Weiss	18-JUL-04	Stock Manager	New Employee

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database