Sql joins: Difference between revisions

From wikinotes
(Created page with "= JOIN = <source lang="mySQL"> # 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...")
 
 
(2 intermediate revisions by the same user not shown)
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>


= JOIN ON Multiple Conditions =
== Subqueries ==
<source lang="mySQL">
<blockquote>
SELECT              *
You can also join on a nested query.<br>
FROM                episodeTable as et
See [[sql queries]] for an example.
INNER JOIN          projectTable pj
ON (
    et.project_Id  = pj.project_Id
and  et.episodeNum  = pj.project_Id
)
</source>


= Negative LEFT JOIN =
</blockquote><!-- Subqueries -->
<code>WHERE NOT IN</code> is known to be terribly inneficient.
</blockquote><!-- Joins -->
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>

Latest revision as of 19:19, 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

Subqueries

You can also join on a nested query.
See sql queries for an example.

Retrieved from "http://willpittman.net:8080/index.php?title=Sql_joins&oldid=27637"