select from where field not equal to field in other table

I am running the following script in SQLite3:

drop table if exists B;
drop table if exists C;

create table B (a integer, b text, c text);

insert into B values (1, "1.1", "B");
insert into B values (1, "2.1", "B");
insert into B values (3, "3.1", "B");
insert into B values (4, "4.1", "B");
insert into B values (5, "5.1", "B");
insert into B values (6, "6.1", "B");
insert into B values (7, "7.1", "B");

create table C (a integer);

insert into C (a) values (1), (3), (5);

select * from B where B.a <> C.a;

This script throws an error when I run it:

Error: near line 30: no such column: C.a

The idea here is that I would like to make a selection from B where field a is not equal to 1, 3 or 5. Is it not possible to refer to columns from other tables within the same SQL statement? How to achieve this?

EXPECTED RESULT

a|b|c
4|"4.1"|"B"
6|"6.1"|"B"
7|"7.1"|"B"

Answer

This requirement can be expressed with NOT EXISTS like this:

select * from B 
where not exists (
  select 1 from C
  where C.a = B.a
);

For every row of B if B.a does not exist in C then NOT EXISTS is TRUE and that row is returned.

Or with a LEFT JOIN from which only the unmatched rows of B are returned:

select B.*
from B left join C
on C.a = B.a
where C.a is null;

See the demo.
Results:

| a   | b   | c   |
| --- | --- | --- |
| 4   | 4.1 | B   |
| 6   | 6.1 | B   |
| 7   | 7.1 | B   |