Sql joins
From wikinotes
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