Sql joins

JOIN

# ALIASES
SELECT a.title, a.name AS Composers_Name, s.name AS Songwriters_name  # You can Alias tablenames for readability
FROM artist a, song s                                                 # from this point on table 'artist' is known by 'a'
                                                                      # and table 'song' is known as 's'

# INNER JOIN
SELECT *                                                              ## INNER JOIN
FROM orders T1, orderdetails T2                                       # Alias 'orders' as 'T1', orderdetails as 'T2'
INNER JOIN T2 ON T1.orderNumber = T2.orderNumber                      # Return rows where 'T1' orderNumber match 'T2' orderNumber

# LEFT JOIN
SELECT *                                                              ## LEFT JOIN
FROM orders T1, orderdetails T2                                       # All results from T1 are printed,
LEFT JOIN T2 ON T1.orderNumber = T2.orderNumber                       # only matching results from T2 are printed

# SUBQUERY
SELECT * FROM pets WHERE pet_Id IN (                                  # You can nest a query in a query
    SELECT pet_Id FROM petTypes WHERE furry=1
)

JOIN ON Multiple Conditions

SELECT              *
FROM                episodeTable as et
INNER JOIN          projectTable pj
ON (
     et.project_Id  = pj.project_Id
and  et.episodeNum  = pj.project_Id
)

Negative LEFT JOIN

WHERE NOT IN is known to be terribly inneficient. The following is a workaround using joins that allows you to perform a LEFT JOIN but removing the inner join segment.

SELECT       * 
FROM         maya_database.projectTable

## Joining table to self imperfectly,
## not all projects have status 'active'
##
LEFT JOIN    maya_database.projectTable  AS  aaa
ON           aaa.project_Id               =  projectTable.project_Id 
AND          aaa.activeStatus             = 'active'

## only select entries where PRIMARY KEY is null
## (generally this would be impossible)
##
where aaa.project_Id IS NULL