SQL JOIN

 SQL JOIN

If two tables already exist, and the 2 tales have a dependency column between them, this permits you to do a JOIN operation.

The format looks like this:

SELECT column1 column2 FROM dependent table INNER JOIN independent table ON interconnected dependent table's column3 = independent table's column4

 Columns 1 2 3 and 4 can all be different.

Column1 --> Column3 --> Column4 --> Column2

will return Column1, Column2 pairs

There is an optional AS keyword variable for aliasing a database x.y variable.

A LEFT JOIN shows all named rows of Column1 even if no match. A None is returned for Column2 in the cases of no matches; this may omit some Column2 values from being displayed.

A RIGHT JOIN guarantees to return all unique Column2 values, even if no matches to Column1 exist. This may omit some Column1 values and will return None for Column1 if there is no match.

Example

players table

columns: ID name sport

Sport table

columns: ID name

players table - the dependent table

1 John 3

2 Sam 2

3 Mary 

Mary has no sport

Sport table - the INdependent table

1 baseball

2 golf

3 basketball

SELECT players.name sport.name FROM players INNER JOIN sport ON players.sport = sport.id

will return

('John', 'basketball')

('Sam', 'golf')

If you don't want to use players.name, you can shorten it as ply

players.name AS ply

LEFT JOIN

SELECT players.name sport.name FROM players LEFT JOIN sport ON players.sport = sport.id

will return all players names no matter what

('John', 'basketball')

('Sam', 'golf')

('Mary', 'None)

Note that baseball is omitted.

RIGHT JOIN

SELECT players.name sport.name FROM players RIGHT JOIN sport ON players.sport = sport.id

will return all sport names no matter what

('John', 'basketball')

('Sam', 'golf')

(None, 'baseball')

Note that Mary is omitted.

Another thing to remember about LEFT and RIGHT JOIN is that multiplicity of rows may occur, one-for-one with multiple matches on the crossover column.

SELECT A.a, B.b

FROM A

RIGHT JOIN B ON A.c = B.c

results in all rows of B are guaranteed to be returned. If any row of B does not contain a match with A on the mutually shared-in-common column c, then column c will be NULL in the A.a field in the result for that row in B. If any row of B does contain 1 or more matches with A on the mutually shared-in-common column c, then column c will be populated uniquely one-for-one in the A.a field with the one or more rows of B that match. This means that the resultant number of rows will contain  the number of rows equal to the total number of matches against A.c on B.c

-----------------------------------

SELECT A.a, B.b

FROM A

LEFT JOIN B ON A.c = B.c

results in all rows of A are guaranteed to be returned. If any row of A does not contain a match with B on the mutually shared-in-common column c, then column c will be NULL in the B.b field in the result for that row in A. If any row of A does contain 1 or more matches with B on the mutually shared-in-common column c, then column c will be populated uniquely one-for-one in the B.b field with the one or more rows of A that match. This means that the resultant number of rows will contain  the number of rows equal to the total number of matches against B.c on A.c