Sql joins: Difference between revisions

From wikinotes
m (Will moved page Sql: joins to Sql joins without leaving a redirect)
No edit summary
Line 1: Line 1:
= JOIN =
= Joins =
<source lang="mySQL">
<blockquote>
# ALIASES
== Overview ==
SELECT a.title, a.name AS Composers_Name, s.name AS Songwriters_name  # You can Alias tablenames for readability
<blockquote>
FROM artist a, song s                                                # from this point on table 'artist' is known by 'a'
Joins let you
                                                                      # and table 'song' is known as 's'
* query rows from multiple tables at once
* filter your table based on an association to another table


# INNER JOIN
<syntaxhighlight lang="MySQL">
SELECT *                                                             ## INNER JOIN
SELECT *                                   # select all rows from 'users' and from 'department_users' tables
FROM orders T1, orderdetails T2                                      # Alias 'orders' as 'T1', orderdetails as 'T2'
FROM users
INNER JOIN T2 ON T1.orderNumber = T2.orderNumber                      # Return rows where 'T1' orderNumber match 'T2' orderNumber
INNER JOIN department_users                # only return subset of rows where the 'ON' condition is met
ON department_users.user_id = users.id    # what determines a match? multiple conditions can be defined
</syntaxhighlight>
</blockquote><!-- Overview -->


# LEFT JOIN
== Types ==
SELECT *                                                              ## LEFT JOIN
<blockquote>
FROM orders T1, orderdetails T2                                      # All results from T1 are printed,
There are generally at least 3x types of joins.
LEFT JOIN T2 ON T1.orderNumber = T2.orderNumber                      # only matching results from T2 are printed


# SUBQUERY
* <code>INNER JOIN</code> - rows where match-condition is met
SELECT * FROM pets WHERE pet_Id IN (                                  # You can nest a query in a query
* <code>LEFT JOIN</code> - all rows in src-table, columns in join-table without a match are all NULL
    SELECT pet_Id FROM petTypes WHERE furry=1
* <code>RIGHT JION</code> - all rows in join-table, columns in src-tabl without a match are all NULL
)
</blockquote><!-- Types -->
</source>
</blockquote><!-- Joins -->
 
= JOIN ON Multiple Conditions =
<source lang="mySQL">
SELECT              *
FROM                episodeTable as et
INNER JOIN          projectTable pj
ON (
    et.project_Id  = pj.project_Id
and  et.episodeNum  = pj.project_Id
)
</source>
 
= Negative LEFT JOIN =
<code>WHERE NOT IN</code> is known to be terribly inneficient.
The following is a workaround using joins that allows you to
perform a <code>LEFT JOIN</code> but removing the inner join segment.
<source lang="mySQL">
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
</source>

Revision as of 19:17, 19 September 2021

Joins

Overview

Joins let you

  • query rows from multiple tables at once
  • filter your table based on an association to another table
SELECT *                                   # select all rows from 'users' and from 'department_users' tables
FROM users
INNER JOIN department_users                # only return subset of rows where the 'ON' condition is met
ON department_users.user_id = users.id     # what determines a match? multiple conditions can be defined

Types

There are generally at least 3x types of joins.

  • INNER JOIN - rows where match-condition is met
  • LEFT JOIN - all rows in src-table, columns in join-table without a match are all NULL
  • RIGHT JION - all rows in join-table, columns in src-tabl without a match are all NULL
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_joins&oldid=27636"