Innies, Outies in SQL #bellybuttons

SQL

Sep 11

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.

SELECT * FROM `movies` CROSS JOIN `members`

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

READ MORE

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.