test objects design

Here is your UseCase Narrative with examples of sql design of INNER JOIN and LEFT JOIN and some confusion that may arrise from using it.
You just opened a shop which sells instruments. To determine if you have any clients you call Chopin, Mozart, Bach and Picasso to see if they will be your clients. They all say yes, Picasso was unsure if would buy an insrument from you but he’ll be happy to be on the list of your clients.

Here is your code for sqlite3 sql
(NOTE: First sqlite3 and set up test.db.
Set your parameters for sqlite3 as such: .mode column, .headers ON and you can also set BLANK as a string instead of getting empty field but setting .nullvalue BLANK)

create table clients(id integer primary key, name varchar(20));
insert into clients values (1,"Chopin");
insert into clients values (2,"Mozart");
insert into clients values (3,"Bach");
insert into clients values (4, "Picasso");
select * from clients;

  id          name
  ----------  ----------
  1           Chopin
  2           Mozart
  3           Bach
  4           Picasso

You decide to carry the following products. Piano, Violin, Cello and Flute based on your clients list. You wanted to get some paint for Picasso but you are not sure when that will arrive and maybe you think you will only sell to musicians and not painters.

create table products(id integer primary key, name varchar(20));
insert into products values (1, "Piano");
insert into products values (2, "Violin");
insert into products values (3, "Cello");
insert into products values (4, "Flute");
select * from products;

  id          name
  ----------  ----------
  1           Piano
  2           Violin
  3           Cello
  4           Flute

You now call each one of the clients and see who will buy what from you. You make a third table that refers to the first two tables you created.

create table orders (id integer, cid integer, pid integer);
insert into orders values (1, 1, 1);
insert into orders values (2, 1, 1);
insert into orders values (3, 2, 1);
insert into orders values (4, 3, 2);
insert into orders values (5, 3, 3);
insert into orders values (6, 2, 2);
select * from orders;

  id          cid         pid
  ----------  ----------  ----------
  1           1           1
  2           1           1
  3           2           1
  4           3           2
  5           3           3
  6           2           2

So now we have three tables.
Instead of clientid we want to know the name of a client who ordered the product
so we can use INNER JOIN.

select orders.id as orderid,
clients.name as customer,
products.name as instrument
from orders
inner join clients
on clients.id=orders.cid
inner join products
on products.id=orders.pid;

  orderid     customer    instrument
  ----------  ----------  ----------
  1           Chopin      Piano
  2           Chopin      Piano
  3           Mozart      Piano
  4           Bach        Violin
  5           Bach        Cello
  6           Mozart      Violin

INNER JOIN will not tell you who did not make an order from CLIENTS table.

So Picasso did not order anything but he is not listed here. To make him appear as a client who didn’t place an order we need to use LEFT JOIN. (NOTE: RIGHT JOIN is not supported in SQLite3)

select orders.id as orderid, clients.name as customer, products.name as instrument
from orders
left join clients
on clients.id=orders.cid
left join products
on products.id=orders.pid;

The above sql will produce the same table even though we changed from INNER JOIN to LEFT JOIN. Picasso is not listed becasue we do LEFT JOIN clients and it is ORDERS table that determines what shows up first. Lets start with CLIENTS table and then LEFT JOIN on ORDERS to fix this (btw: I think this is a testCase to run in DB testing to check how sql is created)

select orders.id as orderid, clients.name as customer, products.name as instrument
from clients
left join orders
on clients.id=orders.cid
left join products
on products.id=orders.pid;

  orderid     customer    instrument
  ----------  ----------  ----------
  1           Chopin      Piano
  2           Chopin      Piano
  3           Mozart      Piano
  6           Mozart      Violin
  4           Bach        Violin
  5           Bach        Cello
              Picasso

Now Picasso shows up but as you can see since this was a CLIENTS table that started the select statement we go by CLIENTS records first and for each client we list order id and instrument.

So finally, who ordered from us?

select distinct clients.name as customer
from clients
left join orders
on clients.id=orders.cid;

  customer
  ----------
  Chopin
  Mozart
  Bach
  Picasso

WOW. Incorrect. We know Picasso didn’t make any orders so why is he here?
Well, because of LEFT JOIN. We should use INNER JOIN instead now.

select distinct clients.name as customer
from clients
inner join orders
on clients.id=orders.cid;

  customer
  ----------
  Chopin
  Mozart
  Bach