SQL Inner Join and Outer Join

Inner Join and Outer Join are used to query information from two database tables. Using them in combination or repeatedly, you can query information from multiple tables.

For example, your organization has an artist table containing all the artists including all dancers and singers, which has a artistId column , a dancer table that has a dancerId column, and a singer table that has a singerId column.  Both the dancerId in the dancer table and the singerId in the singer table reference the artistId in the artist table.

Columns in the artist table: artistId, name, phone, address
Columns in the dancer table: dancerId, style, level
Columns in the singer table: singerId, style, level

Inner Join: Returns only those records that the joining condition matches. For example to list the names and styles of the dancers only:

SELECT a.name, b.style
FROM artist a
Inner Join dancer b on a.artistId = b.dancerId;

Left Outer Join: Returns all the records from the table or from previous Joins before the current Left Outer Join and only those records that match the joining condition from the table following the Left Outer Join. For example to list all the artists including both dancers and singers and the dancing styles of those who are dancers:

SELECT a.name, b.style as "dancing style"
FROM artist a
Left Outer Join dancer b on a.artistId = b.dancerId;

Right Outer Join is the opposite of Left Outer Join. It returns only those records that the joining condition matches from the table or previous Joins before the current Right Outer Join and all the records from the table following the Right Outer Join. For example to list the name and style of all the dancers and the sing style of those who are also singers:

SELECT a.name, b.style as "dancing style", c.style as "singing style"
FROM singer c
Right Outer Join dancer b on b.dancerId = c.singerId
Inner Join artist a on a.artistId = b.dancerId;

Full Outer Join returns all the records match as well as those don't match the joining condition from both sides of the Full Outer Join. For example to list the names, the dancing styles, and the singing styles of all the artists:

SELECT a.name, b.style as "dancing style", c.style as "singing style"
FROM artist a
Full Outer Join dancer b on a.artistId = b.dancerId 
Full Outer Join singer c on a.artistId = c.dancerId;

The Join Predicate is the part following the word "on". In the above Righ Outer Join example, the b.dancerid = c.singerId and a.artistId = b.dancerId are join predicates.

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

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.