September 11

0 comments

Innies, Outies in SQL #bellybuttons

By NickLitten

September 11, 2019


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:

idfirst_namelast_namemovie_id
1AdamSmith1
2RaviKumar2
3SusanDavidson5
4JennyAdrianna8
6LeePong10
idtitlecategory
1ASSASSIN’S CREED: EMBERSAnimations
2Real Steel(2012)Animations
3Alvin and the ChipmunksAnimations
4The Adventures of Tin TinAnimations
5Safe (2012)Action
6Safe House(2012)Action
7GIA18+
8Deadline 200918+
9The Dirty Picture18+
10Marley and meRomance

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.

Innies, outies in sql #bellybuttons 1
SELECT * FROM `movies` CROSS JOIN `members`

Executing the above script in MySQL workbench gives us the following results….

READ MORE

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>