SQL Exercises: Employees who did not have any job in the past
SQL SUBQUERY: Exercise-55 with Solution
From the following table, write a SQL query to find those employees who have not had a job in the past. Return all the fields of employees.
Sample table: employees+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 2005-09-21 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 2001-01-13 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 2005-06-25 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 2006-02-05 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 2007-02-07 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 2002-08-17 | FI_MGR | 12008.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | | 110 | John | Chen | JCHEN | 515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 | 0.00 | 108 | 100 | | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 2005-09-30 | FI_ACCOUNT | 7700.00 | 0.00 | 108 | 100 | | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 2006-03-07 | FI_ACCOUNT | 7800.00 | 0.00 | 108 | 100 | | 113 | Luis | Popp | LPOPP | 515.124.4567 | 2007-12-07 | FI_ACCOUNT | 6900.00 | 0.00 | 108 | 100 | | 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 2002-12-07 | PU_MAN | 11000.00 | 0.00 | 100 | 30 | | 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 2003-05-18 | PU_CLERK | 3100.00 | 0.00 | 114 | 30 | | 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 2005-12-24 | PU_CLERK | 2900.00 | 0.00 | 114 | 30 | | 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 2005-07-24 | PU_CLERK | 2800.00 | 0.00 | 114 | 30 | | 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 2006-11-15 | PU_CLERK | 2600.00 | 0.00 | 114 | 30 | | 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 2007-08-10 | PU_CLERK | 2500.00 | 0.00 | 114 | 30 | | 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 2004-07-18 | ST_MAN | 8000.00 | 0.00 | 100 | 50 | | 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 2005-04-10 | ST_MAN | 8200.00 | 0.00 | 100 | 50 | | 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 2003-05-01 | ST_MAN | 7900.00 | 0.00 | 100 | 50 | | 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 2005-10-10 | ST_MAN | 6500.00 | 0.00 | 100 | 50 | | 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 2007-11-16 | ST_MAN | 5800.00 | 0.00 | 100 | 50 | | 125 | Julia | Nayer | JNAYER | 650.124.1214 | 2005-07-16 | ST_CLERK | 3200.00 | 0.00 | 120 | 50 | | 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 2006-09-28 | ST_CLERK | 2700.00 | 0.00 | 120 | 50 | | 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.00 | 0.00 | 120 | 50 | | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2008-03-08 | ST_CLERK | 2200.00 | 0.00 | 120 | 50 | | 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 2005-08-20 | ST_CLERK | 3300.00 | 0.00 | 121 | 50 | | 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 2005-10-30 | ST_CLERK | 2800.00 | 0.00 | 121 | 50 | | 131 | James | Marlow | JAMRLOW | 650.124.7234 | 2005-02-16 | ST_CLERK | 2500.00 | 0.00 | 121 | 50 | | 132 | TJ | Olson | TJOLSON | 650.124.8234 | 2007-04-10 | ST_CLERK | 2100.00 | 0.00 | 121 | 50 | | 133 | Jason | Mallin | JMALLIN | 650.127.1934 | 2004-06-14 | ST_CLERK | 3300.00 | 0.00 | 122 | 50 | | 134 | Michael | Rogers | MROGERS | 650.127.1834 | 2006-08-26 | ST_CLERK | 2900.00 | 0.00 | 122 | 50 | | 135 | Ki | Gee | KGEE | 650.127.1734 | 2007-12-12 | ST_CLERK | 2400.00 | 0.00 | 122 | 50 | | 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | 2008-02-06 | ST_CLERK | 2200.00 | 0.00 | 122 | 50 | | 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | 2003-07-14 | ST_CLERK | 3600.00 | 0.00 | 123 | 50 | | 138 | Stephen | Stiles | SSTILES | 650.121.2034 | 2005-10-26 | ST_CLERK | 3200.00 | 0.00 | 123 | 50 | | 139 | John | Seo | JSEO | 650.121.2019 | 2006-02-12 | ST_CLERK | 2700.00 | 0.00 | 123 | 50 | | 140 | Joshua | Patel | JPATEL | 650.121.1834 | 2006-04-06 | ST_CLERK | 2500.00 | 0.00 | 123 | 50 | | 141 | Trenna | Rajs | TRAJS | 650.121.8009 | 2003-10-17 | ST_CLERK | 3500.00 | 0.00 | 124 | 50 | | 142 | Curtis | Davies | CDAVIES | 650.121.2994 | 2005-01-29 | ST_CLERK | 3100.00 | 0.00 | 124 | 50 | | 143 | Randall | Matos | RMATOS | 650.121.2874 | 2006-03-15 | ST_CLERK | 2600.00 | 0.00 | 124 | 50 | | 144 | Peter | Vargas | PVARGAS | 650.121.2004 | 2006-07-09 | ST_CLERK | 2500.00 | 0.00 | 124 | 50 | | 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | 2004-10-01 | SA_MAN | 14000.00 | 0.40 | 100 | 80 | | 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | 2005-01-05 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | | 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | 2005-03-10 | SA_MAN | 12000.00 | 0.30 | 100 | 80 | | 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | 2007-10-15 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | | 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | 2008-01-29 | SA_MAN | 10500.00 | 0.20 | 100 | 80 | | 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 2005-01-30 | SA_REP | 10000.00 | 0.30 | 145 | 80 | | 151 | David | Bernstein | DBERNSTE | 011.44.1344.345268 | 2005-03-24 | SA_REP | 9500.00 | 0.25 | 145 | 80 | | 152 | Peter | Hall | PHALL | 011.44.1344.478968 | 2005-08-20 | SA_REP | 9000.00 | 0.25 | 145 | 80 | | 153 | Christopher | Olsen | COLSEN | 011.44.1344.498718 | 2006-03-30 | SA_REP | 8000.00 | 0.20 | 145 | 80 | | 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | 2006-12-09 | SA_REP | 7500.00 | 0.20 | 145 | 80 | | 155 | Oliver | Tuvault | OTUVAULT | 011.44.1344.486508 | 2007-11-23 | SA_REP | 7000.00 | 0.15 | 145 | 80 | | 156 | Janette | King | JKING | 011.44.1345.429268 | 2004-01-30 | SA_REP | 10000.00 | 0.35 | 146 | 80 | | 157 | Patrick | Sully | PSULLY | 011.44.1345.929268 | 2004-03-04 | SA_REP | 9500.00 | 0.35 | 146 | 80 | | 158 | Allan | McEwen | AMCEWEN | 011.44.1345.829268 | 2004-08-01 | SA_REP | 9000.00 | 0.35 | 146 | 80 | | 159 | Lindsey | Smith | LSMITH | 011.44.1345.729268 | 2005-03-10 | SA_REP | 8000.00 | 0.30 | 146 | 80 | | 160 | Louise | Doran | LDORAN | 011.44.1345.629268 | 2005-12-15 | SA_REP | 7500.00 | 0.30 | 146 | 80 | | 161 | Sarath | Sewall | SSEWALL | 011.44.1345.529268 | 2006-11-03 | SA_REP | 7000.00 | 0.25 | 146 | 80 | | 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 2005-11-11 | SA_REP | 10500.00 | 0.25 | 147 | 80 | | 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 2007-03-19 | SA_REP | 9500.00 | 0.15 | 147 | 80 | | 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2008-01-24 | SA_REP | 7200.00 | 0.10 | 147 | 80 | | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2008-02-23 | SA_REP | 6800.00 | 0.10 | 147 | 80 | | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2008-03-24 | SA_REP | 6400.00 | 0.10 | 147 | 80 | | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2008-04-21 | SA_REP | 6200.00 | 0.10 | 147 | 80 | | 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 2005-03-11 | SA_REP | 11500.00 | 0.25 | 148 | 80 | | 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 2006-03-23 | SA_REP | 10000.00 | 0.20 | 148 | 80 | | 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 2006-01-24 | SA_REP | 9600.00 | 0.20 | 148 | 80 | | 171 | William | Smith | WSMITH | 011.44.1343.629268 | 2007-02-23 | SA_REP | 7400.00 | 0.15 | 148 | 80 | | 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 2007-03-24 | SA_REP | 7300.00 | 0.15 | 148 | 80 | | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2008-04-21 | SA_REP | 6100.00 | 0.10 | 148 | 80 | | 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 2004-05-11 | SA_REP | 11000.00 | 0.30 | 149 | 80 | | 175 | Alyssa | Hutton | AHUTTON | 011.44.1644.429266 | 2005-03-19 | SA_REP | 8800.00 | 0.25 | 149 | 80 | | 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | 2006-03-24 | SA_REP | 8600.00 | 0.20 | 149 | 80 | | 177 | Jack | Livingston | JLIVINGS | 011.44.1644.429264 | 2006-04-23 | SA_REP | 8400.00 | 0.20 | 149 | 80 | | 178 | Kimberely | Grant | KGRANT | 011.44.1644.429263 | 2007-05-24 | SA_REP | 7000.00 | 0.15 | 149 | 0 | | 179 | Charles | Johnson | CJOHNSON | 011.44.1644.429262 | 2008-01-04 | SA_REP | 6200.00 | 0.10 | 149 | 80 | | 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | 2006-01-24 | SH_CLERK | 3200.00 | 0.00 | 120 | 50 | | 181 | Jean | Fleaur | JFLEAUR | 650.507.9877 | 2006-02-23 | SH_CLERK | 3100.00 | 0.00 | 120 | 50 | | 182 | Martha | Sullivan | MSULLIVA | 650.507.9878 | 2007-06-21 | SH_CLERK | 2500.00 | 0.00 | 120 | 50 | | 183 | Girard | Geoni | GGEONI | 650.507.9879 | 2008-02-03 | SH_CLERK | 2800.00 | 0.00 | 120 | 50 | | 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | 2004-01-27 | SH_CLERK | 4200.00 | 0.00 | 121 | 50 | | 185 | Alexis | Bull | ABULL | 650.509.2876 | 2005-02-20 | SH_CLERK | 4100.00 | 0.00 | 121 | 50 | | 186 | Julia | Dellinger | JDELLING | 650.509.3876 | 2006-06-24 | SH_CLERK | 3400.00 | 0.00 | 121 | 50 | | 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | 2007-02-07 | SH_CLERK | 3000.00 | 0.00 | 121 | 50 | | 188 | Kelly | Chung | KCHUNG | 650.505.1876 | 2005-06-14 | SH_CLERK | 3800.00 | 0.00 | 122 | 50 | | 189 | Jennifer | Dilly | JDILLY | 650.505.2876 | 2005-08-13 | SH_CLERK | 3600.00 | 0.00 | 122 | 50 | | 190 | Timothy | Gates | TGATES | 650.505.3876 | 2006-07-11 | SH_CLERK | 2900.00 | 0.00 | 122 | 50 | | 191 | Randall | Perkins | RPERKINS | 650.505.4876 | 2007-12-19 | SH_CLERK | 2500.00 | 0.00 | 122 | 50 | | 192 | Sarah | Bell | SBELL | 650.501.1876 | 2004-02-04 | SH_CLERK | 4000.00 | 0.00 | 123 | 50 | | 193 | Britney | Everett | BEVERETT | 650.501.2876 | 2005-03-03 | SH_CLERK | 3900.00 | 0.00 | 123 | 50 | | 194 | Samuel | McCain | SMCCAIN | 650.501.3876 | 2006-07-01 | SH_CLERK | 3200.00 | 0.00 | 123 | 50 | | 195 | Vance | Jones | VJONES | 650.501.4876 | 2007-03-17 | SH_CLERK | 2800.00 | 0.00 | 123 | 50 | | 196 | Alana | Walsh | AWALSH | 650.507.9811 | 2006-04-24 | SH_CLERK | 3100.00 | 0.00 | 124 | 50 | | 197 | Kevin | Feeney | KFEENEY | 650.507.9822 | 2006-05-23 | SH_CLERK | 3000.00 | 0.00 | 124 | 50 | | 198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 2007-06-21 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 199 | Douglas | Grant | DGRANT | 650.507.9844 | 2008-01-13 | SH_CLERK | 2600.00 | 0.00 | 124 | 50 | | 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | 2003-09-17 | AD_ASST | 4400.00 | 0.00 | 101 | 10 | | 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | 2004-02-17 | MK_MAN | 13000.00 | 0.00 | 100 | 20 | | 202 | Pat | Fay | PFAY | 603.123.6666 | 2005-08-17 | MK_REP | 6000.00 | 0.00 | 201 | 20 | | 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 2002-06-07 | HR_REP | 6500.00 | 0.00 | 101 | 40 | | 204 | Hermann | Baer | HBAER | 515.123.8888 | 2002-06-07 | PR_REP | 10000.00 | 0.00 | 101 | 70 | | 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 2002-06-07 | AC_MGR | 12008.00 | 0.00 | 101 | 110 | | 206 | William | Gietz | WGIETZ | 515.123.8181 | 2002-06-07 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+Sample table: job_history
+-------------+------------+------------+------------+---------------+ | EMPLOYEE_ID | START_DATE | END_DATE | JOB_ID | DEPARTMENT_ID | +-------------+------------+------------+------------+---------------+ | 102 | 2001-01-13 | 2006-07-24 | IT_PROG | 60 | | 101 | 1997-09-21 | 2001-10-27 | AC_ACCOUNT | 110 | | 101 | 2001-10-28 | 2005-03-15 | AC_MGR | 110 | | 201 | 2004-02-17 | 2007-12-19 | MK_REP | 20 | | 114 | 2006-03-24 | 2007-12-31 | ST_CLERK | 50 | | 122 | 2007-01-01 | 2007-12-31 | ST_CLERK | 50 | | 200 | 1995-09-17 | 2001-06-17 | AD_ASST | 90 | | 176 | 2006-03-24 | 2006-12-31 | SA_REP | 80 | | 176 | 2007-01-01 | 2007-12-31 | SA_MAN | 80 | | 200 | 2002-07-01 | 2006-12-31 | AC_ACCOUNT | 90 | +-------------+------------+------------+------------+---------------+
Sample Solution:
-- Selecting all columns (*) from the 'employees' table
SELECT *
-- Filtering rows from the 'employees' table based on the condition that the 'employee_id' is not in the result set of a subquery
FROM employees
-- Subquery to find 'employee_id' values where there is no corresponding entry in the 'job_history' table
WHERE employee_id NOT IN
(SELECT employee_id
FROM job_history);
Sample Output:
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id 100 Steven King SKING 515.123.4567 2003-06-17 AD_PRES 24000.00 0.00 0 90 103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 IT_PROG 9000.00 0.00 102 60 104 Bruce Ernst BERNST 590.423.4568 2007-05-21 IT_PROG 6000.00 0.00 103 60 105 David Austin DAUSTIN 590.423.4569 2005-06-25 IT_PROG 4800.00 0.00 103 60 106 Valli Pataballa VPATABAL590.423.4560 2006-02-05 IT_PROG 4800.00 0.00 103 60 107 Diana Lorentz DLORENTZ590.423.5567 2007-02-07 IT_PROG 4200.00 0.00 103 60 108 Nancy Greenberg NGREENBE515.124.4569 2002-08-17 FI_MGR 12000.00 0.00 101 100 109 Daniel Faviet DFAVIET 515.124.4169 2002-08-16 FI_ACCOUNT 9000.00 0.00 108 100 110 John Chen JCHEN 515.124.4269 2005-09-28 FI_ACCOUNT 8200.00 0.00 108 100 111 Ismael Sciarra ISCIARRA515.124.4369 2005-09-30 FI_ACCOUNT 7700.00 0.00 108 100 112 Jose ManuelUrman JMURMAN515.124.4469 2006-03-07 FI_ACCOUNT 7800.00 0.00 108 100 113 Luis Popp LPOPP 515.124.4567 2007-12-07 FI_ACCOUNT 6900.00 0.00 108 100 115 Alexander Khoo AKHOO 515.127.4562 2003-05-18 PU_CLERK 3100.00 0.00 114 30 116 Shelli Baida SBAIDA 515.127.4563 2005-12-24 PU_CLERK 2900.00 0.00 114 30 117 Sigal Tobias STOBIAS 515.127.4564 2005-07-24 PU_CLERK 2800.00 0.00 114 30 118 Guy Himuro GHIMURO 515.127.4565 2006-11-15 PU_CLERK 2600.00 0.00 114 30 119 Karen Colmenares KCOLMENA515.127.4566 2007-08-10 PU_CLERK 2500.00 0.00 114 30 120 Matthew Weiss MWEISS 650.123.1234 2004-07-18 ST_MAN 8000.00 0.00 100 50 121 Adam Fripp AFRIPP 650.123.2234 2005-04-10 ST_MAN 8200.00 0.00 100 50 123 Shanta Vollman SVOLLMAN650.123.4234 2005-10-10 ST_MAN 6500.00 0.00 100 50 124 Kevin Mourgos KMOURGOS650.123.5234 2007-11-16 ST_MAN 5800.00 0.00 100 50 125 Julia Nayer JNAYER 650.124.1214 2005-07-16 ST_CLERK 3200.00 0.00 120 50 126 Irene Mikkilineni IMIKKILI650.124.1224 2006-09-28 ST_CLERK 2700.00 0.00 120 50 127 James Landry JLANDRY 650.124.1334 2007-01-14 ST_CLERK 2400.00 0.00 120 50 128 Steven Markle SMARKLE 650.124.1434 2008-03-08 ST_CLERK 2200.00 0.00 120 50 129 Laura Bissot LBISSOT 650.124.5234 2005-08-20 ST_CLERK 3300.00 0.00 121 50 130 Mozhe Atkinson MATKINSO650.124.6234 2005-10-30 ST_CLERK 2800.00 0.00 121 50 131 James Marlow JAMRLOW 650.124.7234 2005-02-16 ST_CLERK 2500.00 0.00 121 50 132 TJ Olson TJOLSON 650.124.8234 2007-04-10 ST_CLERK 2100.00 0.00 121 50 133 Jason Mallin JMALLIN 650.127.1934 2004-06-14 ST_CLERK 3300.00 0.00 122 50 134 Michael Rogers MROGERS 650.127.1834 2006-08-26 ST_CLERK 2900.00 0.00 122 50 135 Ki Gee KGEE 650.127.1734 2007-12-12 ST_CLERK 2400.00 0.00 122 50 136 Hazel Philtanker HPHILTAN650.127.1634 2008-02-06 ST_CLERK 2200.00 0.00 122 50 137 Renske Ladwig RLADWIG 650.121.1234 2003-07-14 ST_CLERK 3600.00 0.00 123 50 138 Stephen Stiles SSTILES 650.121.2034 2005-10-26 ST_CLERK 3200.00 0.00 123 50 139 John Seo JSEO 650.121.2019 2006-02-12 ST_CLERK 2700.00 0.00 123 50 140 Joshua Patel JPATEL 650.121.1834 2006-04-06 ST_CLERK 2500.00 0.00 123 50 141 Trenna Rajs TRAJS 650.121.8009 2003-10-17 ST_CLERK 3500.00 0.00 124 50 142 Curtis Davies CDAVIES 650.121.2994 2005-01-29 ST_CLERK 3100.00 0.00 124 50 143 Randall Matos RMATOS 650.121.2874 2006-03-15 ST_CLERK 2600.00 0.00 124 50 144 Peter Vargas PVARGAS 650.121.2004 2006-07-09 ST_CLERK 2500.00 0.00 124 50 145 John Russell JRUSSEL 011.44.1344.429268 2004-10-01 SA_MAN 14000.00 0.40 100 80 146 Karen Partners KPARTNER011.44.1344.467268 2005-01-05 SA_MAN 13500.00 0.30 100 80 147 Alberto Errazuriz AERRAZUR011.44.1344.429278 2005-03-10 SA_MAN 12000.00 0.30 100 80 148 Gerald Cambrault GCAMBRAU011.44.1344.619268 2007-10-15 SA_MAN 11000.00 0.30 100 80 149 Eleni Zlotkey EZLOTKEY011.44.1344.429018 2008-01-29 SA_MAN 10500.00 0.20 100 80 150 Peter Tucker PTUCKER 011.44.1344.129268 2005-01-30 SA_REP 10000.00 0.30 145 80 151 David Bernstein DBERNSTE011.44.1344.345268 2005-03-24 SA_REP 9500.00 0.25 145 80 152 Peter Hall PHALL 011.44.1344.478968 2005-08-20 SA_REP 9000.00 0.25 145 80 153 Christopher Olsen COLSEN 011.44.1344.498718 2006-03-30 SA_REP 8000.00 0.20 145 80 154 Nanette Cambrault NCAMBRAU011.44.1344.987668 2006-12-09 SA_REP 7500.00 0.20 145 80 155 Oliver Tuvault OTUVAULT011.44.1344.486508 2007-11-23 SA_REP 7000.00 0.15 145 80 156 Janette King JKING 011.44.1345.429268 2004-01-30 SA_REP 10000.00 0.35 146 80 157 Patrick Sully PSULLY 011.44.1345.929268 2004-03-04 SA_REP 9500.00 0.35 146 80 158 Allan McEwen AMCEWEN 011.44.1345.829268 2004-08-01 SA_REP 9000.00 0.35 146 80 159 Lindsey Smith LSMITH 011.44.1345.729268 2005-03-10 SA_REP 8000.00 0.30 146 80 160 Louise Doran LDORAN 011.44.1345.629268 2005-12-15 SA_REP 7500.00 0.30 146 80 161 Sarath Sewall SSEWALL 011.44.1345.529268 2006-11-03 SA_REP 7000.00 0.25 146 80 162 Clara Vishney CVISHNEY011.44.1346.129268 2005-11-11 SA_REP 10500.00 0.25 147 80 163 Danielle Greene DGREENE 011.44.1346.229268 2007-03-19 SA_REP 9500.00 0.15 147 80 164 Mattea Marvins MMARVINS011.44.1346.329268 2008-01-24 SA_REP 7200.00 0.10 147 80 165 David Lee DLEE 011.44.1346.529268 2008-02-23 SA_REP 6800.00 0.10 147 80 166 Sundar Ande SANDE 011.44.1346.629268 2008-03-24 SA_REP 6400.00 0.10 147 80 167 Amit Banda ABANDA 011.44.1346.729268 2008-04-21 SA_REP 6200.00 0.10 147 80 168 Lisa Ozer LOZER 011.44.1343.929268 2005-03-11 SA_REP 11500.00 0.25 148 80 169 Harrison Bloom HBLOOM 011.44.1343.829268 2006-03-23 SA_REP 10000.00 0.20 148 80 170 Tayler Fox TFOX 011.44.1343.729268 2006-01-24 SA_REP 9600.00 0.20 148 80 171 William Smith WSMITH 011.44.1343.629268 2007-02-23 SA_REP 7400.00 0.15 148 80 172 Elizabeth Bates EBATES 011.44.1343.529268 2007-03-24 SA_REP 7300.00 0.15 148 80 173 Sundita Kumar SKUMAR 011.44.1343.329268 2008-04-21 SA_REP 6100.00 0.10 148 80 174 Ellen Abel EABEL 011.44.1644.429267 2004-05-11 SA_REP 11000.00 0.30 149 80 175 Alyssa Hutton AHUTTON 011.44.1644.429266 2005-03-19 SA_REP 8800.00 0.25 149 80 177 Jack Livingston JLIVINGS011.44.1644.429264 2006-04-23 SA_REP 8400.00 0.20 149 80 178 Kimberely Grant KGRANT 011.44.1644.429263 2007-05-24 SA_REP 7000.00 0.15 149 0 179 Charles Johnson CJOHNSON011.44.1644.429262 2008-01-04 SA_REP 6200.00 0.10 149 80 180 Winston Taylor WTAYLOR 650.507.9876 2006-01-24 SH_CLERK 3200.00 0.00 120 50 181 Jean Fleaur JFLEAUR 650.507.9877 2006-02-23 SH_CLERK 3100.00 0.00 120 50 182 Martha Sullivan MSULLIVA650.507.9878 2007-06-21 SH_CLERK 2500.00 0.00 120 50 183 Girard Geoni GGEONI 650.507.9879 2008-02-03 SH_CLERK 2800.00 0.00 120 50 184 Nandita Sarchand NSARCHAN650.509.1876 2004-01-27 SH_CLERK 4200.00 0.00 121 50 185 Alexis Bull ABULL 650.509.2876 2005-02-20 SH_CLERK 4100.00 0.00 121 50 186 Julia Dellinger JDELLING650.509.3876 2006-06-24 SH_CLERK 3400.00 0.00 121 50 187 Anthony Cabrio ACABRIO 650.509.4876 2007-02-07 SH_CLERK 3000.00 0.00 121 50 188 Kelly Chung KCHUNG 650.505.1876 2005-06-14 SH_CLERK 3800.00 0.00 122 50 189 Jennifer Dilly JDILLY 650.505.2876 2005-08-13 SH_CLERK 3600.00 0.00 122 50 190 Timothy Gates TGATES 650.505.3876 2006-07-11 SH_CLERK 2900.00 0.00 122 50 191 Randall Perkins RPERKINS650.505.4876 2007-12-19 SH_CLERK 2500.00 0.00 122 50 192 Sarah Bell SBELL 650.501.1876 2004-02-04 SH_CLERK 4000.00 0.00 123 50 193 Britney Everett BEVERETT650.501.2876 2005-03-03 SH_CLERK 3900.00 0.00 123 50 194 Samuel McCain SMCCAIN 650.501.3876 2006-07-01 SH_CLERK 3200.00 0.00 123 50 195 Vance Jones VJONES 650.501.4876 2007-03-17 SH_CLERK 2800.00 0.00 123 50 196 Alana Walsh AWALSH 650.507.9811 2006-04-24 SH_CLERK 3100.00 0.00 124 50 197 Kevin Feeney KFEENEY 650.507.9822 2006-05-23 SH_CLERK 3000.00 0.00 124 50 198 Donald OConnell DOCONNEL650.507.9833 2007-06-21 SH_CLERK 2600.00 0.00 124 50 199 Douglas Grant DGRANT 650.507.9844 2008-01-13 SH_CLERK 2600.00 0.00 124 50 202 Pat Fay PFAY 603.123.6666 2005-08-17 MK_REP 6000.00 0.00 201 20 203 Susan Mavris SMAVRIS 515.123.7777 2002-06-07 HR_REP 6500.00 0.00 101 40 204 Hermann Baer HBAER 515.123.8888 2002-06-07 PR_REP 10000.000.00 101 70 205 Shelley Higgins SHIGGINS 515.123.8080 2002-06-07 AC_MGR 12000.000.00 101 110 206 William Gietz WGIETZ 515.123.8181 2002-06-07 AC_ACCOUNT 8300.00 0.00 205 110
Code Explanation:
The said query in SQL that selects all the rows from the 'employees' table where the "employee_id" does not match any of the values returned by the subquery. A subquery is used to select the values from the "job_history" table corresponding to employee_id values. Therefore, the query will return all employees who do not have a record in the "job_history" table when it is performed.
Visual Presentation:
Alternative Solutions:
Using a Left Join:
SELECT e.*
FROM employees e
LEFT JOIN job_history jh ON e.employee_id = jh.employee_id
WHERE jh.employee_id IS NULL;
Using NOT EXISTS:
SELECT e.*
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM job_history jh
WHERE jh.employee_id = e.employee_id
);
Using a LEFT JOIN with NULL Check:
SELECT e.*
FROM employees e
LEFT JOIN job_history jh ON e.employee_id = jh.employee_id
WHERE jh.employee_id IS NULL;
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Highest salary drawar in a department.
Next SQL Exercise: SQL JOINS on HR Database Exercises Home
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/sql-subqueries-exercise-55.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics