Self Joins which try to find ‘something in common’ between rows in the same table need extra handling

Notes on Self-Joins

Self Joins which try to find ‘something in common’ between rows in the same table need extra handling. Many examples only cover one possibility, where duplicates will naturally not occur. In this example, we want to see all instructors who were hired in the same year, so to start:

SQL> /

LAST_NAME TO_CHAR( start_date, ‘YYYY’)

-------------------- ----

FLINTSTONE 1990

RUBBLE 1990

FLINTSTONE 1991

I put the blank lines between the years of hire, to show who should come out

FLINTSTONE 1991

WILSON 1991

RUBBLE 1992

GRIMM 1992

RUBBLE 1992

CAPP 1992

CAT 1992

FLINTSTONE 1993

JETSON 1993

JETSON 1994

BAILEY 1994

EMPLOYEE 1994

MCNAMARA 1994

VIKING 1994

JETSON 1995

MENNACE 1995

BABY 1995

BIGHEAD 1995

23 rows selected.

In this sequence, I need to see Flintstone and Rubble on the same line, because

they have same hire year, but watch what happens, because of the ‘relational product’ I naturally get in any join, where elements of one table/view are horizontally attached to elements of the other table(s)/view(s):

1 SELECT L.FIRST_NAME||' '||L.LAST_NAME || ' ' ||

2 TO_CHAR(L.HIRE_DATE, 'YYYY') || ' ' ||

3 R.FIRST_NAME||' '||R.LAST_NAME

4 FROM INSTRUCTOR R, INSTRUCTOR L

5* WHERE TO_CHAR( L.HIRE_DATE, 'YYYY' ) = TO_CHAR( R.HIRE_DATE, 'YYYY' )

SQL> /

L.FIRST_NAME||''||L.LAST_NAME||''||TO_CHAR(L.HIRE_DATE,'YYYY')||''||R.FIRST_NAME||''||R.

----------------------------------------------------------------------------------------

FRED FLINTSTONE 1990 FRED FLINTSTONE

What happens is the self join always produces a useless combination where of course, FRED FLINTSTONE will be found in both tables being joined, and WILMA, and PEBBLES, etc. I need to eliminate those where there is an exact match.

FRED FLINTSTONE 1990 BARNEY RUBBLE

BARNEY RUBBLE 1990 FRED FLINTSTONE

BARNEY RUBBLE 1990 BARNEY RUBBLE

WILMA FLINTSTONE 1991 WILMA FLINTSTONE

WILMA FLINTSTONE 1991 PEBBLES FLINTSTONE

WILMA FLINTSTONE 1991 MR WILSON

PEBBLES FLINTSTONE 1991 WILMA FLINTSTONE

PEBBLES FLINTSTONE 1991 PEBBLES FLINTSTONE

PEBBLES FLINTSTONE 1991 MR WILSON

MR WILSON 1991 WILMA FLINTSTONE

MR WILSON 1991 PEBBLES FLINTSTONE

MR WILSON 1991 MR WILSON

BETTY RUBBLE 1992 BETTY RUBBLE

BETTY RUBBLE 1992 MOTHER GOOSE GRIMM

BETTY RUBBLE 1992 BAMMBAMM RUBBLE

BETTY RUBBLE 1992 ANDY CAPP

BETTY RUBBLE 1992 HEATHCLIFF CAT

MOTHER GOOSE GRIMM 1992 BETTY RUBBLE

MOTHER GOOSE GRIMM 1992 MOTHER GOOSE GRIMM

MOTHER GOOSE GRIMM 1992 BAMMBAMM RUBBLE

MOTHER GOOSE GRIMM 1992 ANDY CAPP

MOTHER GOOSE GRIMM 1992 HEATHCLIFF CAT

BAMMBAMM RUBBLE 1992 BETTY RUBBLE

BAMMBAMM RUBBLE 1992 MOTHER GOOSE GRIMM

BAMMBAMM RUBBLE 1992 BAMMBAMM RUBBLE

BAMMBAMM RUBBLE 1992 ANDY CAPP

BAMMBAMM RUBBLE 1992 HEATHCLIFF CAT

ANDY CAPP 1992 BETTY RUBBLE

ANDY CAPP 1992 MOTHER GOOSE GRIMM

ANDY CAPP 1992 BAMMBAMM RUBBLE

ANDY CAPP 1992 ANDY CAPP

ANDY CAPP 1992 HEATHCLIFF CAT

. . .

GEORGE JETSON 1993 DINO FLINTSTONE

GEORGE JETSON 1993 GEORGE JETSON

GEORGE JETSON 1993 JANE JETSON

99 rows selected.

This is the query with the extra criterion to eliminate the right side/left side duplicate:

1 SELECT L.FIRST_NAME||' '||L.LAST_NAME || ' ' ||

2 TO_CHAR(L.HIRE_DATE, 'YYYY') || ' ' ||

3 R.FIRST_NAME||' '||R.LAST_NAME

4 FROM INSTRUCTOR R, INSTRUCTOR L

5 WHERE TO_CHAR( L.HIRE_DATE, 'YYYY' ) = TO_CHAR( R.HIRE_DATE, 'YYYY' )

6 AND L.LAST_NAME <> R.LAST_NAME AND L.FIRST_NAME <> R.FIRST_NAME

7* ORDER BY 1

SQL> /

L.FIRST_NAME||''||L.LAST_NAME||''||TO_CHAR(L.HIRE_DATE,'YYYY')||''||R.FIRST_NAME||''||R.

----------------------------------------------------------------------------------------

Here’s the next natural outcome of this type operation, where the two combinations of ‘left vs. right’ AND ‘right vs. left’ will surface.

ANDY CAPP 1992 BAMMBAMM RUBBLE

ANDY CAPP 1992 BETTY RUBBLE

ANDY CAPP 1992 HEATHCLIFF CAT

ANDY CAPP 1992 MOTHER GOOSE GRIMM

BAMMBAMM RUBBLE 1992 ANDY CAPP

BAMMBAMM RUBBLE 1992 HEATHCLIFF CAT

BAMMBAMM RUBBLE 1992 MOTHER GOOSE GRIMM

BARNEY RUBBLE 1990 FRED FLINTSTONE

BEETLE BAILEY 1994 DILBERT EMPLOYEE

BEETLE BAILEY 1994 HAGAR VIKING

BEETLE BAILEY 1994 JUDY JETSON

BEETLE BAILEY 1994 ROSIE JETSON

BEETLE BAILEY 1994 TANK MCNAMARA

. . .

ROSIE JETSON 1994 DILBERT EMPLOYEE

ROSIE JETSON 1994 HAGAR VIKING

ROSIE JETSON 1994 TANK MCNAMARA

TANK MCNAMARA 1994 BEETLE BAILEY

TANK MCNAMARA 1994 DILBERT EMPLOYEE

TANK MCNAMARA 1994 HAGAR VIKING

TANK MCNAMARA 1994 JUDY JETSON

TANK MCNAMARA 1994 ROSIE JETSON

WILMA FLINTSTONE 1991 MR WILSON

ZIGGY BIGHEAD 1995 DENNIS MENNACE

ZIGGY BIGHEAD 1995 ELROY JETSON

ZIGGY BIGHEAD 1995 MARVIN BABY

68 rows selected.

This last required piece seems like an almost-arbitrary condition, but we use Greater than or Less than to disqualify one of the pairings.

1 SELECT L.FIRST_NAME||' '||L.LAST_NAME || ' ' ||

2 TO_CHAR(L.HIRE_DATE, 'YYYY') || ' ' ||

3 R.FIRST_NAME||' '||R.LAST_NAME

4 FROM INSTRUCTOR R, INSTRUCTOR L

5 WHERE TO_CHAR( L.HIRE_DATE, 'YYYY' ) = TO_CHAR( R.HIRE_DATE, 'YYYY' )

6 AND L.LAST_NAME <> R.LAST_NAME AND L.FIRST_NAME <> R.FIRST_NAME

7 AND L.LAST_NAME || L.FIRST_NAME > R.LAST_NAME || R.FIRST_NAME

8* ORDER BY TO_CHAR( L.HIRE_DATE, 'YYYY' )

SQL> /

BARNEY RUBBLE 1990 FRED FLINTSTONE

MR WILSON 1991 WILMA FLINTSTONE

MR WILSON 1991 PEBBLES FLINTSTONE

BETTY RUBBLE 1992 MOTHER GOOSE GRIMM

BETTY RUBBLE 1992 ANDY CAPP

BETTY RUBBLE 1992 HEATHCLIFF CAT

MOTHER GOOSE GRIMM 1992 ANDY CAPP

MOTHER GOOSE GRIMM 1992 HEATHCLIFF CAT

BAMMBAMM RUBBLE 1992 MOTHER GOOSE GRIMM

BAMMBAMM RUBBLE 1992 ANDY CAPP

BAMMBAMM RUBBLE 1992 HEATHCLIFF CAT

HEATHCLIFF CAT 1992 ANDY CAPP

GEORGE JETSON 1993 DINO FLINTSTONE

JANE JETSON 1993 DINO FLINTSTONE

JUDY JETSON 1994 BEETLE BAILEY

JUDY JETSON 1994 DILBERT EMPLOYEE

ROSIE JETSON 1994 BEETLE BAILEY

ROSIE JETSON 1994 DILBERT EMPLOYEE

DILBERT EMPLOYEE 1994 BEETLE BAILEY

TANK MCNAMARA 1994 JUDY JETSON

TANK MCNAMARA 1994 ROSIE JETSON

TANK MCNAMARA 1994 BEETLE BAILEY

TANK MCNAMARA 1994 DILBERT EMPLOYEE

HAGAR VIKING 1994 JUDY JETSON

HAGAR VIKING 1994 ROSIE JETSON

HAGAR VIKING 1994 BEETLE BAILEY

HAGAR VIKING 1994 DILBERT EMPLOYEE

HAGAR VIKING 1994 TANK MCNAMARA

ELROY JETSON 1995 MARVIN BABY

ELROY JETSON 1995 ZIGGY BIGHEAD

DENNIS MENNACE 1995 ELROY JETSON

DENNIS MENNACE 1995 MARVIN BABY

DENNIS MENNACE 1995 ZIGGY BIGHEAD

ZIGGY BIGHEAD 1995 MARVIN BABY

34 rows selected.

To summarize: Side-by-side comparisons are available in one query with Self-Joins, but self-joins based on a common value can naturally create a number of redundant matches, so these steps showed you how to deal with them.