[PDF] joins-cheat-sheet-a4.pdf SQL JOINs Cheat Sheet. NATURAL

View - Download joins-cheat-sheet-a4.pdf

Previous PDF

Next PDF

Try out the interactive SQL JOINs course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

SQL JOINs Cheat Sheet

NATURAL JOIN

If the tables have columns with

the same name, you can use

NATURAL JOIN instead of JOIN.

The common column appears only once in the result table. Note: NATURAL JOIN is rarely used in real life.SELECT *

FROM toy

NATURAL JOIN cat;

LEFT JOIN

LEFT JOIN returns all rows from the ȅюݦ

with

NULLs. LEFT JOIN is also called LEFT OUTER JOIN.

SELECT *

FROM toy

LEFT JOIN cat

ON toy.cat_id = cat.cat_id;

NULLNULL

RIGHT JOIN

RIGHT JOIN returns all rows from the ȅю

NULLs. RIGHT JOIN is also called RIGHT OUTER JOIN.

SELECT *

FROM toy

RIGHT JOIN cat

ON toy.cat_id = cat.cat_id;

NULLNULLNULL2Hugo

1ball33Sam

4mouse44Misty

FULL JOIN

FULL JOIN returns all rows from the ȅ and all rows from the юݦѣ

NULLs. FULL JOIN is also called FULL OUTER JOIN.

SELECT *

FROM toy

FULL JOIN cat

ON toy.cat_id = cat.cat_id;

NULLNULLNULL2Hugo

1ball33Sam

4mouse44Misty

2springNULLNULLNULL

CROSS JOIN

CROSS JOIN returns ȅюtoy_idtoy_namecat_idcat_idcat_name

1ball31Kitty

2springNULL1Kitty

3mouse11Kitty

4mouse41Kitty

5ball11Kitty

1ball32Hugo

2springNULL2Hugo

3mouse12Hugo

4mouse42Hugo

5ball12Hugo

1ball33Sam

CROSS JOIN cat;

SELECT *

FROM toy, cat;Other syntax:

JOIN JOIN returns all rows that match the ON condition. JOIN is also called INNER JOIN.

SELECT *

FROM toy

JOIN cat

ON toy.cat_id = cat.cat_id;

There is also another, older syntax, but it ї.

List joined tables in the

FROM clause, and place the conditions in the WHERE clause.

SELECT *

FROM toy, cat

WHERE toy.cat_id = cat.cat_id;JOIN combines data from two tables.

JOINING TABLES

JOINݦюUsually, one table contains a primary key, which is a column or columns that uniquely identify rows in the table (the cat_id column in the cat table).

The other table has a column or columns that

cat_id column in the

toy table). Such columns are foreign keys. The JOIN condition is the equality between the primary key columns in

one table and columns referring to them in the other table. CAT cat_idcat_name

1Kitty

2Hugo 3Sam

4MistyTOY

toy_idtoy_namecat_id

1ball3

2springNULL

3mouse1

4mouse4

5ball1

JOIN CONDITIONS

The

JOIN condition doesn't have to be an equality - it can be any condition you want. JOIN doesn't interpret the JOIN

condition, it only checks if the rows satisfy the given condition.

To refer to a column in the

column name:

ON cat.cat_id = toy.cat_id

You can omit the table name and use just the column name if the name of the column is unique within all columns in the

joined tables. Try out the interactive SQL JOINs course at LearnSQL.com, and check out our other SQL courses.

LearnSQL.com is owned by Vertabelo SA

vertabelo.com | CC BY-NC-ND Vertabelo SA

SQL JOINs Cheat Sheet

COLUMN AND TABLE ALIASES

Aliases give a temporary name to a

or a in a table. A you must use it instead of the table name everywhere in the query. The

ASݦю

OWNER AS o

idname

1John Smith

2Danielle DavisCAT AS c

cat_idcat_namemom_idowner_id

1Kitty51

2Hugo12

3Sam22

4Misty1NULL

o.name AS owner_name, c.cat_name

FROM cat AS c

JOIN owner AS o

ON c.owner_id = o.id;

owner_name

KittyJohn Smith

SamDanielle Davis

HugoDanielle Davis

SELF JOIN

CAT AS child

cat_idcat_nameowner_idmom_id

1Kitty15

2Hugo21

3Sam22

4

MistyNULL1CAT AS mom

cat_idcat_nameowner_idmom_id

1Kitty15

2Hugo21

3Sam22

4

MistyNULL1

Each occurrence of the table must be given a

Ȃ. Each column reference must be preceded with an

SELECT

child.cat_name AS child_name, mom.cat_name AS mom_name

FROM cat AS child

JOIN cat AS mom

ON child.mom_id = mom.cat_id;

NON-EQUI SELF JOIN

You can use a

non-equality in the ON condition, for example, to show Ȃ of rows.

TOY AS a

toy_idtoy_namecat_id

3mouse1

5ball1

1ball3

4mouse4

2springNULLTOY AS b

cat_idtoy_idtoy_name

13mouse

15ball

31ball

44mouse

NULL2spring

a.cat_id < b.cat_id; cat_a_idtoy_acat_b_idtoy_b

1mouse3ball

1ball3ball

1mouse4mouse

1ball4mouse

3ball4mouse

MULTIPLE JOINS

You can join more than two tables together. First, two tables are joined, then the third table is joined to the result of the

previous joining.

TOY AS t

toy_idtoy_namecat_id

1ball3

2springNULL

3mouse1

4mouse4

5ball1

cat_idcat_namemom_idowner_id

1Kitty51

2Hugo12

3Sam22

4Misty1NULL

OWNER AS o

idname 1 John Smith 2

Danielle

Davis

JOIN & JOINLEFT JOIN & LEFT JOINJOIN & LEFT JOIN

SELECT

t.toy_name, c.cat_name, o.name

AS owner_name

FROM toy t

JOIN cat c

ON t.cat_id = c.cat_id

JOIN owner o

ON c.owner_id = o.id;SELECT

t.toy_name, c.cat_name, o.name

AS owner_name

FROM toy t

JOIN cat c

ON t.cat_id = c.cat_id

LEFT JOIN owner o

ON c.owner_id = o.id;SELECT

t.toy_name, c.cat_name, o.name

AS owner_name

FROM toy t

LEFT JOIN cat c

ON t.cat_id = c.cat_id

LEFT JOIN owner o

ON c.owner_id = o.id;

JOIN WITH MULTIPLE CONDITIONS

You can use multiple JOIN conditions using the

ON keyword once and the AND keywords as many times as you need. cat_idcat_namemom_idowner_idage

1Kitty5117

2Hugo1210

3Sam225

4Misty1NULL11OWNER AS o

idnameage

1John Smith18

2Danielle Davis10

ON c.owner_id = o.id

AND c.age < o.age;

quotesdbs_dbs21.pdfusesText_27

[PDF] mysql interview questions pdf

[PDF] mysql workbench apple

[PDF] mythological achilles

[PDF] mythologie grecque pdf

[PDF] mythos and logos

[PDF] mythos et logos

[PDF] myuhc gym membership

[PDF] mywifiext

[PDF] myww blue meal plan

[PDF] n acylation acetic anhydride

[PDF] n coupled harmonic oscillators

[PDF] n coupled oscillators

[PDF] n tier architecture business layer

[PDF] n tier architecture data layer

[PDF] n tier architecture presentation layer