PostgreSQL

psql_4tbl_join.md

sqlfiddle

Tables

CREATE TABLE courses(
  course_id int(10),
  course_name varchar(255)
);
      
INSERT INTO courses (course_id, course_name)
VALUES  (511, 'Web Designing'),
        (512, 'Web Development'),
        (513, 'Programming');

CREATE TABLE subjects(
  subject_id int(10),
  subject_name varchar(255),
  course_id int(10)
);
      
INSERT INTO subjects (subject_id, subject_name, course_id)
VALUES  (221, 'HTML', 511),
        (222, 'CSS', 511),
        (223, 'Javascript', 511),
        (224, 'PHP', 512),
        (225, 'Python', 512),
        (226, '.Net', 512),
        (227, 'Java', 513),
        (228, 'C++', 513);
                     
CREATE TABLE chapters(
  chapter_id int(10),
  chapter_name varchar(255),
  subject_id int(10)
);
      
INSERT INTO chapters (chapter_id, chapter_name, subject_id)
VALUES  (101, 'HTML Text', 221),
        (102, 'HTML Image', 221),
        (103, 'HTML List', 221),
        (104, 'HTML Link', 221),
        (105, 'HTML Form', 221),
        (106, 'CSS Border',222),
        (107, 'CSS Position',222),
        (108, 'CSS Selector', 222),
        (109, 'PHP Conditions',224),
        (110, 'PHP Arrays', 224),
        (111, 'Java Methods', 227);

CREATE TABLE subchapters(
  subchapter_id int(10),
  subchapter_name varchar(255),
  chapter_id int(10)
);
      
INSERT INTO subchapters (subchapter_id, subchapter_name, chapter_id)
VALUES  (1201, 'HTML Paragraph', 101),
        (1202, 'HTML Heading', 101),
        (1203, 'HTML Ordered List', 103),
        (1204, 'HTML Unordered List', 103),
        (1205, 'HTML Text Link', 104),
        (1206, 'HTML Image Link',104),
        (1207, 'CSS Border Style',106),
        (1208, 'CSS Border Width', 106),
        (1209, 'CSS Border Color', 106),
        (1210, 'CSS Absolute Position', 107),
        (1211, 'CSS Element Selector', 108),
        (1212, 'PHP if Condition', 109),
        (1213, 'PHP Switch COndition', 109),
        (1214, 'PHP Index Array', 110),
        (1215, 'PHP Associative Array', 110),
        (1216, 'Java Method Parameter', 111),
        (1217, 'Java Method Overloading', 111),
        (1218, 'SQL INNER JOIN', 224);

Query

SELECT courses.course_name, courses.course_id,
       subjects.subject_name,subjects.subject_id,
       chapters.chapter_name, chapters.chapter_id,
       subchapters.subchapter_name, subchapters.subchapter_id
FROM courses
LEFT JOIN subjects ON courses.course_id = subjects.course_id
LEFT JOIN chapters ON subjects.subject_id = chapters.subject_id
LEFT JOIN subchapters ON chapters.chapter_id = subchapters.chapter_id;

psql_joins.md

Creating the tables

create table car {
  id bigserial not null primary key,
  make varchar(100) not null,
  model varchar(100) not null,
  price numeric(19,2) not null
};

create table person {
  id bigserial not null primary key,
  first_name varchar(50) not null,
  middle_name varchar(50),
  last_name varchar(50) not null,
  email varchar(100),
  date_birth date not null,
  country_birth varchar(50) not null,
  car_id bigint references car(id),
  unique(car_id)
};

insert into person (first_name, middle_name, last_name, email, date_birth, country_birth)
values ("Fernanda", null, "Beardon", "[email protected]", "1979-04-12", "Germany");

insert into person (first_name, middle_name, last_name, email, date_birth, country_birth)
values ("Bernard", null, "Colmore", "[email protected]", "1980-07-22", "Finland");

insert into person (first_name, middle_name, last_name, email, date_birth, country_birth)
values ("Adriana", null, "Smith", "[email protected]", "1990-02-12", "United Kingdom");

insert into car (make, model, price) values ("Land Rover", "Sterling", "87654.32");
insert into car (make, model, price) values ("GMC", "Acadia", "17654.32");

-- add GMC to Fernanda
update person set car_id = 2 where id = 1;

-- add Land Rover to Bernard
update person set car_id = 1 where id = 2;

INNER JOIN

• combines two tables and creates RESULT table on foreign key
• if foreign key not present, then row won't be included

-- all columns
select * from person 
join car on person.car_id = car.id;

-- certain columns
select person.first_name, car.make, car.model, car.price
from person
join car on person.car_id = car.id;

LEFT JOIN

• combines two tables on foreign key and creates RESULT table with all rows

-- all columns
select * from person 
left join car on person.car_id = car.id;

-- all columns and show only row with empty car columns
select * from person 
left join car on person.car_id = car.id
where car.* is null;

EXPORT JOIN Table from psql

\copy (SELECT * FROM person LEFT JOIN car ON car.id = person.car_id) TO "path/to/file.csv" DELIMITER "," CSV HEADER;