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 condition
WHERE (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

Inner join

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.

output

• INNER JOINis the keyword to join the table (keyword differ for all the joins)
• ON for making conditions
• AS 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.idit 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 13and also no NULL values because of the movieDetails.iddon't have 13

Full outer join

Again SQLite was not supporting full outer join so here we use unionand 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 WHEREto 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 baliased 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

Tony Wilson