Mastering in SQLite
SQLite-join
Before getting into SQLite-join, if you haven’t covered the previous topic-Set operators and case query, then get into it.
To visit the introduction page to see the available topics click here
A number of superheroes are available in our sample DB, click here to download it.
Normal peoples join in a relationship but developer do join with tables
SQLite Joins
A JOIN is a combining field from two or more tables by using values common to each
- Inner join: return all rows from multiple tables when the join condition is met.
- Left join: returns all rows from the left table, even if there are no matches in the right table
- Right join: result set that includes all rows in the right table, whether or not they have matching rows from the left table
- Full outer join: the combination of left and right join
- cross join: used when you wish to create a combination of every row from two tables
- self join: join a table to itself
- Aliases: assigning a temporary name
Join syntax
select
column_name
FROM
table_name
JOIN second_table_name ON conditionWHERE (if needed, and we can also place any condition here)
condition
Inner Join
Its selects rows from both participating tables only if both tables meet the conditions specified in the ON clause
Example
characterList
(Table-1) and characterDetails
(Table-2)
SELECT
characterList.id,
characterList.name,
characterList.weapons AS powerOfHeros,
charactersDetails.superpower
FROM
characterList
INNER JOIN characterSDetails ON charactersDetails.id = characterList.id
Here we are selecting id, name, and weapons from characterList
(table -1 ) and superpower from characterDetails
(table -2 )
Now INNER JOIN will join the tables by comparing the id columns if both were matched.
INNER JOIN
is the keyword to join the table (keyword differ for all the joins)ON
for making conditionsAS
temporary names for columns or tables (Here I changed weapons AS `powerOfHeros
) (NOTE: take a look below explained more)
you can use powerOfHeros
in other condition too, like
SELECT
characterList.id,
characterList.name,
characterList.weapons AS powerOfHeros,
charactersDetails.superpower
from
characterList
INNER JOIN characterSDetails ON charactersDetails.id = characterList.id
WHERE powerOfHeros = " daggers"
LEFT JOIN
Left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching take place.
Example:
The left table named as characterList and right asmovieDetails
SELECT
characterList.id,
movieDetails.movieName,
characterList.name AS "HERO OF THE MOVIE"
FROM
characterList
LEFT JOIN movieDetails ON characterList.id = movieDetails.id
Look at id no. 13, MovieName is null because characterList.id
it has 13 and movieDetails.id
doesn't have it. so Null produced. (Left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching take place.
Right join
Right join is not supported in SQLite but we can use left join by just replacing the left table as of right and right table as of left
same example from the left join
SELECT
characterList.id,
movieDetails.movieName,
characterList.name AS "HERO OF THE MOVIE"
FROM
movieDetails
left JOIN characterList ON characterList.id = movieDetails.id
swapped the movieDetails
and characterList
look here there is no id 13
and also no NULL values because of the movieDetails.id
don't have 13
Full outer join
Again SQLite was not supporting full outer join so here we use union
and left join
to overcome this problem
Full outer join is a combination of left and right join
The left table named as characterList and right as movieDetails
SELECT
characterList.id,
movieDetails.movieName,
characterList.name as "HERO OF THE MOVIE"
FROM
characterList
left JOIN movieDetails ON characterList.id = movieDetails.id
UNION
SELECT
characterList.id,
movieDetails.movieName,
characterList.name as "HERO OF THE MOVIE"
FROM
movieDetails
left JOIN characterList ON characterList.id = movieDetails.id
union
to remove the duplicate, you can also use union all
but it will not remove duplicate
cross join
If you miss no
conditions, while performing inner or left, will get cross join clause. Often use left or inner than cross (free advice) or be careful while using cross join
SELECT * FROM table1 join table2
or
SELECT * FROM table1 inner join table2
Combination of every row from two tables,
Example
characterList
(table -1) and movieDetails
(table -2)
SELECT
movieDetails.movieName,
characterList.name,
characterList.studio
FROM
characterList
CROSS JOIN movieDetails
WHERE
characterList.studio = "disney"
I used WHERE
to reduce no. of rows even though it goes on
output
Self-join
In past joins we joined with two or more tables, here table join itself (single table)
Example
SELECT
a.name,
a.studio
FROM
characterList a
INNER JOIN characterList b on a.name = b.movieName
characterList a , a.name
are ALIASES will explain below
Here I used inner join
we can use any join here
OUTPUT
ALIASES
ALIASES used to make temporary names for columns or tables
Syntax
column_name AS alias_name
or
table_name alias_name
Example
SELECT
characterList.id,
characterList.name,
characterList.weapons AS powerOfHeros,
charactersDetails.superpower
FROM
characterList
INNER JOIN characterSDetails ON charactersDetails.id = characterList.id
WHERE powerOfHeros = " daggers"
Note: This example from an inner join
AS keyword used when aliasing a column name, but not when aliasing a table name
ALIAS for table
SELECT
a.name,
a.studio
FROM
characterList a
INNER JOIN characterList b on a.name = b.movieName
Note: an example from self.join
a and b
aliased for characterList
this is how aliases did for the table,
Aliases mostly use in self-join or to shorten the SQLite query
Joining three tables
It’s very simple, just add another join query after the first join (Inner join)
SELECT
characterList.id,
characterList.name,
characterList.weapons AS powerOfHeros,
charactersDetails.superpower
FROM
characterList
INNER JOIN characterSDetails ON charactersDetails.id = characterList.id
INNER JOIN movieDetails ON movieDetails.id = characterList.id
don’t ask output for this, just work out with given sample DB and find out the results
Reach out the next topic -Subquery or nested query