Going for the Guinness Book of Records for vague blog titles I decided to waffle about inner joins and outer joins in the world of bellybuttons *cough* Structured Query Language.
Since plagiarism is the best form of flattery, Chirag Sharma (over at Guru99) wrote a very nice article about the finer points of SQL INNER JOINS and OUTER JOINS:
MySQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS
What are JOINS?
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Note: JOIN is the most misunderstood topic among SQL learners. For sake of simplicity and ease of understanding lets look at this example:
| id | first_name | last_name | movie_id |
|---|---|---|---|
| 1 | Adam | Smith | 1 |
| 2 | Ravi | Kumar | 2 |
| 3 | Susan | Davidson | 5 |
| 4 | Jenny | Adrianna | 8 |
| 6 | Lee | Pong | 10 |
| id | title | category |
|---|---|---|
| 1 | ASSASSIN’S CREED: EMBERS | Animations |
| 2 | Real Steel(2012) | Animations |
| 3 | Alvin and the Chipmunks | Animations |
| 4 | The Adventures of Tin Tin | Animations |
| 5 | Safe (2012) | Action |
| 6 | Safe House(2012) | Action |
| 7 | GIA | 18+ |
| 8 | Deadline 2009 | 18+ |
| 9 | The Dirty Picture | 18+ |
| 10 | Marley and me | Romance |
Types of joins
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
Suppose we want to get all member records against all the movie records, we can use the script shown below to get our desired results.
SELECT * FROM `movies` CROSS JOIN `members`
Executing the above script in MySQL workbench gives us the following results….