Home > Back-end >  Oracle Self-Join
Oracle Self-Join

Time：11-27

Im working through some self-join examples and I am drawing a blank on the following example. Its the last example at the following link Self-Join Example

SELECT
   e1.hire_date,
  (e1.first_name || ' ' || e1.last_name) employee1,
  (e2.first_name || ' ' || e2.last_name) employee2  
FROM
    employees e1
INNER JOIN employees e2 ON
    e1.employee_id > e2.employee_id
    AND e1.hire_date = e2.hire_date
ORDER BY  
   e1.hire_date DESC,
   employee1, 
   employee2;

I dont understand why I need to check if Employee_id from the first instance of employee is greater than employee_id from the second instance of employee.

If anyone can provide a good explanation for this, it would be greatly appreciated.

Required create/insert scripts.

CREATE TABLE employees
  (
    employee_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 108 
                PRIMARY KEY,
    first_name VARCHAR( 255 ) NOT NULL,
    last_name  VARCHAR( 255 ) NOT NULL,
    email      VARCHAR( 255 ) NOT NULL,
    phone      VARCHAR( 50 ) NOT NULL ,
    hire_date  DATE NOT NULL          ,
    manager_id NUMBER( 12, 0 )        , -- fk
    job_title  VARCHAR( 255 ) NOT NULL,
    CONSTRAINT fk_employees_manager 
        FOREIGN KEY( manager_id )
        REFERENCES employees( employee_id )
        ON DELETE CASCADE
  );

Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (107,'Summer','Payne','[email protected]','515.123.8181',to_date('07-JUN-16','DD-MON-RR'),106,'Public Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (106,'Rose','Stephens','[email protected]','515.123.8080',to_date('07-JUN-16','DD-MON-RR'),2,'Accounting Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (101,'Annabelle','Dunn','[email protected]','515.123.4444',to_date('17-SEP-16','DD-MON-RR'),2,'Administration Assistant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (1,'Tommy','Bailey','[email protected]','515.123.4567',to_date('17-JUN-16','DD-MON-RR'),null,'President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (3,'Blake','Cooper','[email protected]','515.123.4569',to_date('13-JAN-16','DD-MON-RR'),1,'Administration Vice President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (2,'Jude','Rivera','[email protected]','515.123.4568',to_date('21-SEP-16','DD-MON-RR'),1,'Administration Vice President');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (11,'Tyler','Ramirez','[email protected]','515.124.4269',to_date('28-SEP-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (10,'Ryan','Gray','[email protected]','515.124.4169',to_date('16-AUG-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (14,'Elliot','Brooks','[email protected]','515.124.4567',to_date('07-DEC-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (12,'Elliott','James','[email protected]','515.124.4369',to_date('30-SEP-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (13,'Albert','Watson','[email protected]','515.124.4469',to_date('07-MAR-16','DD-MON-RR'),9,'Accountant');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (9,'Mohammad','Peterson','[email protected]','515.124.4569',to_date('17-AUG-16','DD-MON-RR'),2,'Finance Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (104,'Harper','Spencer','[email protected]','515.123.7777',to_date('07-JUN-16','DD-MON-RR'),2,'Human Resources Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (4,'Louie','Richardson','[email protected]','590.423.4567',to_date('03-JAN-16','DD-MON-RR'),3,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (5,'Nathan','Cox','[email protected]','590.423.4568',to_date('21-MAY-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (8,'Bobby','Torres','[email protected]','590.423.5567',to_date('07-FEB-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (7,'Charles','Ward','[email protected]','590.423.4560',to_date('05-FEB-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (6,'Gabriel','Howard','[email protected]','590.423.4569',to_date('25-JUN-16','DD-MON-RR'),4,'Programmer');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (102,'Emma','Perkins','[email protected]','515.123.5555',to_date('17-FEB-16','DD-MON-RR'),1,'Marketing Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (103,'Amelie','Hudson','[email protected]','603.123.6666',to_date('17-AUG-16','DD-MON-RR'),102,'Marketing Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (105,'Gracie','Gardner','[email protected]','515.123.8888',to_date('07-JUN-16','DD-MON-RR'),2,'Public Relations Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (17,'Frederick','Price','[email protected]','515.127.4563',to_date('24-DEC-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (16,'Alex','Sanders','[email protected]','515.127.4562',to_date('18-MAY-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (18,'Ollie','Bennett','[email protected]','515.127.4564',to_date('24-JUL-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (19,'Louis','Wood','[email protected]','515.127.4565',to_date('15-NOV-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (20,'Dexter','Barnes','[email protected]','515.127.4566',to_date('10-AUG-16','DD-MON-RR'),15,'Purchasing Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (15,'Rory','Kelly','[email protected]','515.127.4561',to_date('07-DEC-16','DD-MON-RR'),1,'Purchasing Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (49,'Isabella','Cole','[email protected]','011.44.1344.619268',to_date('15-OCT-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (48,'Jessica','Woods','[email protected]','011.44.1344.429278',to_date('10-MAR-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (47,'Ella','Wallace','[email protected]','011.44.1344.467268',to_date('05-JAN-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (46,'Ava','Sullivan','[email protected]','011.44.1344.429268',to_date('01-OCT-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (50,'Mia','West','[email protected]','011.44.1344.429018',to_date('29-JAN-16','DD-MON-RR'),1,'Sales Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (56,'Evie','Harrison','[email protected]','011.44.1344.486508',to_date('23-NOV-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (57,'Scarlett','Gibson','[email protected]','011.44.1345.429268',to_date('30-JAN-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (58,'Ruby','Mcdonald','[email protected]','011.44.1345.929268',to_date('04-MAR-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (59,'Chloe','Cruz','[email protected]','011.44.1345.829268',to_date('01-AUG-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (60,'Isabelle','Marshall','[email protected]','011.44.1345.729268',to_date('10-MAR-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (61,'Daisy','Ortiz','[email protected]','011.44.1345.629268',to_date('15-DEC-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (62,'Freya','Gomez','[email protected]','011.44.1345.529268',to_date('03-NOV-16','DD-MON-RR'),47,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (80,'Elizabeth','Dixon','[email protected]','011.44.1644.429262',to_date('04-JAN-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (64,'Florence','Freeman','[email protected]','011.44.1346.229268',to_date('19-MAR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (65,'Alice','Wells','[email protected]','011.44.1346.329268',to_date('24-JAN-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (66,'Charlotte','Webb','[email protected]','011.44.1346.529268',to_date('23-FEB-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (67,'Sienna','Simpson','[email protected]','011.44.1346.629268',to_date('24-MAR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (68,'Matilda','Stevens','[email protected]','011.44.1346.729268',to_date('21-APR-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (69,'Evelyn','Tucker','[email protected]','011.44.1343.929268',to_date('11-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (70,'Eva','Porter','[email protected]','011.44.1343.829268',to_date('23-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (71,'Millie','Hunter','[email protected]','011.44.1343.729268',to_date('24-JAN-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (72,'Sofia','Hicks','[email protected]','011.44.1343.629268',to_date('23-FEB-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (73,'Lucy','Crawford','[email protected]','011.44.1343.529268',to_date('24-MAR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (74,'Elsie','Henry','[email protected]','011.44.1343.329268',to_date('21-APR-16','DD-MON-RR'),49,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (75,'Imogen','Boyd','[email protected]','011.44.1644.429267',to_date('11-MAY-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (76,'Layla','Mason','[email protected]','011.44.1644.429266',to_date('19-MAR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (77,'Rosie','Morales','[email protected]','011.44.1644.429265',to_date('24-MAR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (78,'Maya','Kennedy','[email protected]','011.44.1644.429264',to_date('23-APR-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (79,'Esme','Warren','[email protected]','011.44.1644.429263',to_date('24-MAY-16','DD-MON-RR'),50,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (55,'Grace','Ellis','[email protected]','011.44.1344.987668',to_date('09-DEC-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (54,'Lily','Fisher','[email protected]','011.44.1344.498718',to_date('30-MAR-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (53,'Sophia','Reynolds','[email protected]','011.44.1344.478968',to_date('20-AUG-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (52,'Sophie','Owens','[email protected]','011.44.1344.345268',to_date('24-MAR-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (51,'Poppy','Jordan','[email protected]','011.44.1344.129268',to_date('30-JAN-16','DD-MON-RR'),46,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (63,'Phoebe','Murray','[email protected]','011.44.1346.129268',to_date('11-NOV-16','DD-MON-RR'),48,'Sales Representative');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (85,'Holly','Shaw','[email protected]','650.509.1876',to_date('27-JAN-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (86,'Emilia','Holmes','[email protected]','650.509.2876',to_date('20-FEB-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (87,'Molly','Rice','[email protected]','650.509.3876',to_date('24-JUN-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (88,'Ellie','Robertson','[email protected]','650.509.4876',to_date('07-FEB-16','DD-MON-RR'),22,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (89,'Jasmine','Hunt','[email protected]','650.505.1876',to_date('14-JUN-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (90,'Eliza','Black','[email protected]','650.505.2876',to_date('13-AUG-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (91,'Lilly','Daniels','[email protected]','650.505.3876',to_date('11-JUL-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (92,'Abigail','Palmer','[email protected]','650.505.4876',to_date('19-DEC-16','DD-MON-RR'),23,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (93,'Georgia','Mills','[email protected]','650.501.1876',to_date('04-FEB-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (94,'Maisie','Nichols','[email protected]','650.501.2876',to_date('03-MAR-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (95,'Eleanor','Grant','[email protected]','650.501.3876',to_date('01-JUL-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (96,'Hannah','Knight','[email protected]','650.501.4876',to_date('17-MAR-16','DD-MON-RR'),24,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (97,'Harriet','Ferguson','[email protected]','650.507.9811',to_date('24-APR-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (98,'Amber','Rose','[email protected]','650.507.9822',to_date('23-MAY-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (99,'Bella','Stone','[email protected]','650.507.9833',to_date('21-JUN-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (100,'Thea','Hawkins','[email protected]','650.507.9844',to_date('13-JAN-16','DD-MON-RR'),25,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (81,'Lola','Ramos','[email protected]','650.507.9876',to_date('24-JAN-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (82,'Willow','Reyes','[email protected]','650.507.9877',to_date('23-FEB-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (83,'Ivy','Burns','[email protected]','650.507.9878',to_date('21-JUN-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (84,'Erin','Gordon','[email protected]','650.507.9879',to_date('03-FEB-16','DD-MON-RR'),21,'Shipping Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (33,'Reggie','Simmons','[email protected]','650.124.8234',to_date('10-APR-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (44,'Emily','Hamilton','[email protected]','650.121.2874',to_date('15-MAR-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (43,'Olivia','Ford','[email protected]','650.121.2994',to_date('29-JAN-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (42,'Amelia','Myers','[email protected]','650.121.8009',to_date('17-OCT-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (41,'Connor','Hayes','[email protected]','650.121.1834',to_date('06-APR-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (26,'Leon','Powell','[email protected]','650.124.1214',to_date('16-JUL-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (27,'Kai','Long','[email protected]','650.124.1224',to_date('28-SEP-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (28,'Aaron','Patterson','[email protected]','650.124.1334',to_date('14-JAN-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (29,'Roman','Hughes','[email protected]','650.124.1434',to_date('08-MAR-16','DD-MON-RR'),21,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (30,'Austin','Flores','[email protected]','650.124.5234',to_date('20-AUG-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (31,'Ellis','Washington','[email protected]','650.124.6234',to_date('30-OCT-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (32,'Jamie','Butler','[email protected]','650.124.7234',to_date('16-FEB-16','DD-MON-RR'),22,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (45,'Isla','Graham','[email protected]','650.121.2004',to_date('09-JUL-16','DD-MON-RR'),25,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (34,'Seth','Foster','[email protected]','650.127.1934',to_date('14-JUN-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (35,'Carter','Gonzales','[email protected]','650.127.1834',to_date('26-AUG-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (36,'Felix','Bryant','[email protected]','650.127.1734',to_date('12-DEC-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (37,'Ibrahim','Alexander','[email protected]','650.127.1634',to_date('06-FEB-16','DD-MON-RR'),23,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (38,'Sonny','Russell','[email protected]','650.121.1234',to_date('14-JUL-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (39,'Kian','Griffin','[email protected]','650.121.2034',to_date('26-OCT-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (40,'Caleb','Diaz','[email protected]','650.121.2019',to_date('12-FEB-16','DD-MON-RR'),24,'Stock Clerk');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (25,'Ronnie','Perry','[email protected]','650.123.5234',to_date('16-NOV-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (24,'Callum','Jenkins','[email protected]','650.123.4234',to_date('10-OCT-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (23,'Jackson','Coleman','[email protected]','650.123.3234',to_date('01-MAY-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (22,'Liam','Henderson','[email protected]','650.123.2234',to_date('10-APR-16','DD-MON-RR'),1,'Stock Manager');
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,MANAGER_ID,JOB_TITLE) values (21,'Jaxon','Ross','[email protected]','650.123.1234',to_date('18-JUL-16','DD-MON-RR'),1,'Stock Manager');

CodePudding user response：

If you didn't have any condition on employee ID at all you'd end up with records where a self-match had occurred, e.g. the results would show "Gracie Gardner was hired on the same day as Gracie Gardner"

We could then put ON e1.employee_id <> e2.employee_id - this would prevent Gracie matching with Gracie, but you'd then find "Gracie Gardner was hired on the same day as Summer Payne" and "Summer Payne was hired on the same day as Gracie Gardner" - i.e. you'd get "duplicate records" in terms of "person paired with person", each name being mentioned both ways round

Using greater than prevents this, and effectively means that any given pair of names only appears once. Because Gracie's ID is less than Summer's, you'll get Gracie in e1 paired with Summer in e2 but you won't get Summer in e1 paired with Gracie in e2

Another way of visualizing it is with a square/matrix

   no id      id1<>id2   id1<id2

     A B C      A B C      A B C
   A * * *    A   * *    A  
   B * * *    B *   *    B *  
   C * * *    C * *      C * *

In the first square you have everything matched with everything. In the second you remove AA / BB / CC matches but you still have AB and BA, AC and CA etc. In the third square you only have uniquely paired letters (AB but not BA, AC but not CA, ...)

CodePudding user response：

If you compare on != (instead of >):

SELECT e1.hire_date,
       e1.first_name || ' ' || e1.last_name AS employee1,
       e2.first_name || ' ' || e2.last_name AS employee2  
FROM   employees e1
       INNER JOIN employees e2
       ON e1.employee_id != e2.employee_id AND e1.hire_date = e2.hire_date
ORDER BY
       e1.hire_date DESC,
       employee1, 
       employee2;

Then you will find you get duplicate rows with the values with the names swapped round:

HIRE_DATE EMPLOYEE1 EMPLOYEE2
07-DEC-16 Elliot Brooks Rory Kelly
07-DEC-16 Rory Kelly Elliot Brooks

If you use just > then you only get one of the two rows as the other is filtered out:

HIRE_DATE EMPLOYEE1 EMPLOYEE2
07-DEC-16 Rory Kelly Elliot Brooks

db<>fiddle here

Page link：https//www.codepudding.com/Backend/206353.html

Prev:Return same select if exists
Next:How to print random from table in a procedure oracle?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding