The Self-JOIN function is used to combine rows with other rows in the same table.
Syntax:
-- Self-JOIN
SELECT t1_column_1
FROM table_1 AS t1, table_2 AS t2
WHERE t1.column_2 = t2.column_2 Example: Retrieve all customers whose last name matched the first name of another customers.(SELF-JOIN)
-- Retrieve all customers whose last name matched the first name of another customers. (SELF-JOIN)
SELECT a.customer_id, a.first_name, a.last_name, b.customer_id, b.first_name, b.last_name
FROM customer AS a, customer AS b
WHERE a.first_name = b.last_name
limit 20;## customer_id first_name last_name customer_id first_name last_name
## 1 65 Rose Howard 157 Darlene Rose
## 2 67 Kelly Torres 74 Denise Kelly
## 3 118 Kim Cruz 233 Lillie Kim
## 4 230 Joy George 307 Joseph Joy
## 5 253 Terry Carlson 265 Jennie Terry
## 6 299 James Gannon 71 Kathy James
## 7 301 Robert Baughman 331 Eric Robert
## 8 308 Thomas Grigsby 12 Nancy Thomas
## 9 312 Mark Rinehart 336 Joshua Mark
## 10 314 George Linton 230 Joy George
## 11 330 Scott Shelley 34 Rebecca Scott
## 12 333 Andrew Purdy 326 Jose Andrew
## 13 335 Gregory Mauldin 284 Sonia Gregory
## 14 343 Douglas Graf 257 Marsha Douglas
## 15 344 Henry Billingsley 133 Pauline Henry
## 16 347 Ryan Salisbury 203 Tara Ryan
## 17 355 Terry Grissom 265 Jennie Terry
## 18 361 Lawrence Lawton 192 Laurie Lawrence
## 19 367 Adam Gooch 504 Nathaniel Adam
## 20 377 Howard Fortner 65 Rose HowardExample: Retrieve all customers whose last name matched the first name of another customers. Rewrite as JOIN statement.
-- Retrieve all customers whose last name matched the first name of another customers. JOIN Statement
SELECT a.customer_id, a.first_name, a.last_name, b.customer_id, b.first_name, b.last_name
FROM customer AS a
JOIN customer AS b
ON a.first_name = b.last_name;## customer_id first_name last_name customer_id first_name last_name
## 1 65 Rose Howard 157 Darlene Rose
## 2 67 Kelly Torres 74 Denise Kelly
## 3 118 Kim Cruz 233 Lillie Kim
## 4 230 Joy George 307 Joseph Joy
## 5 253 Terry Carlson 265 Jennie Terry
## 6 299 James Gannon 71 Kathy James
## 7 301 Robert Baughman 331 Eric Robert
## 8 308 Thomas Grigsby 12 Nancy Thomas
## 9 312 Mark Rinehart 336 Joshua Mark
## 10 314 George Linton 230 Joy George
## 11 330 Scott Shelley 34 Rebecca Scott
## 12 333 Andrew Purdy 326 Jose Andrew
## 13 335 Gregory Mauldin 284 Sonia Gregory
## 14 343 Douglas Graf 257 Marsha Douglas
## 15 344 Henry Billingsley 133 Pauline Henry
## 16 347 Ryan Salisbury 203 Tara Ryan
## 17 355 Terry Grissom 265 Jennie Terry
## 18 361 Lawrence Lawton 192 Laurie Lawrence
## 19 367 Adam Gooch 504 Nathaniel Adam
## 20 377 Howard Fortner 65 Rose Howard
## 21 380 Russell Brinson 97 Annie Russell
## 22 383 Martin Bales 16 Sandra Martin
## 23 388 Craig Morrell 288 Bobbie Craig
## 24 404 Stanley Scroggins 228 Allison Stanley
## 25 410 Curtis Irby 270 Leah Curtis
## 26 412 Allen Butterfield 27 Shirley Allen
## 27 421 Lee Hawks 24 Kimberly Lee
## 28 426 Bradley Motley 181 Ana Bradley
## 29 430 Ray Houle 173 Audrey Ray
## 30 439 Alexander Fennell 96 Diana Alexander
## 31 443 Francisco Skidmore 431 Joel Francisco
## 32 462 Warren Sherrod 138 Hazel Warren
## 33 470 Gordon Allard 143 Leslie Gordon
## 34 471 Dean Sauer 236 Marcia Dean
## 35 478 Lewis Lyman 23 Sarah Lewis
## 36 497 Gilbert Sledge 237 Tanya Gilbert
## 37 514 Franklin Troutman 199 Beth Franklin
## 38 520 Mitchell Westmoreland 41 Stephanie Mitchell
## 39 522 Arnold Havens 170 Beatrice Arnold
## 40 523 Harvey Guajardo 225 Arlene Harvey
## 41 543 Lance Pemberton 420 Jacob Lance
## 42 546 Kelly Knott 74 Denise Kelly
## 43 549 Nelson Christenson 39 Debra Nelson
## 44 560 Jordan Archuleta 110 Tiffany Jordan
## 45 562 Wallace Slone 106 Connie Wallace
## 46 576 Morris Mccarter 53 Heather Morris
## 47 580 Ross Grey 80 Marilyn Ross
## 48 583 Marshall Thorn 119 Sherry Marshall
## 49 585 Perry Swafford 84 Sara Perry
## 50 598 Wade Delvalle 267 Margie Wade
## 51 599 Austin Cintron 196 Alma Austin