What are the key differences between SOQL Joins and SQL Joins?
Below is a comparison inspired by A Deeper look at SOQL and Relationship Queries on Force.com.
(We suppose that we have an Account & Contact tables in a SQL database with the column Account as foreign key from Contact to Account)
Right Outer Join
SELECT FirstName, LastName, Account.Name FROM Contact
SELECT con.FirstName, con.LastName, acc.Name FROM Contact con RIGHT OUTER JOIN Account acc ON con.Account=acc.Id
Left Outer Join
SELECT Name,(SELECT FirstName,LastName FROM Contacts) FROM Account
SELECT acc.Name, con.FirstName, con.LastName FROM Contact con LEFT OUTER JOIN Account acc ON con.Account=acc.Id