[日本語]

Before I forget the knowledge of tables JOIN which I learned by self-study and work site, I want to leave it here as a note. For samples, I would like to use pgAdmin 4 of PostgreSQL tool.

Please refer to this site for detailed instructions on how to install PgAdmin4. In addition, although it is English, this video is also a guide for SQL and on how to install PgAdmin4.

• Table JOIN

Table JOIN in SQL is joining multiple related tables according to the JOIN condition and extracting records.

Example of basic SQL JOIN statement：

SELECT name_of_column
FROM table_A
INNER JOIN table_B
ON join_condition;

Assume that the following two tables exist as sample.

 

• 4 main Table JOINs

 – INNER JOIN

Join table A and table B and extract only the records that match the join condition.

Example of statement：
※ “*” means all.

SELECT * 
FROM student 
INNER JOIN school
ON student.school_id = school.school_id;

 – LEFT OUTER JOIN

Join table A and table B, and extract all the records in table A and records that match the join conditions in table B. Note that columns that did not match the join condition of table B will be NULL.

Example of statement：
※ “*” means all.

SELECT * 
FROM student 
LEFT OUTER JOIN school 
ON student.school_id = school.school_id;

 – RIGHT OUTER JOIN

Join table A and table B, and extract all the records in table B and records that match the join conditions in table A. Note that columns that did not match the join condition of table A will be NULL.

Example of statement：
※ “*” means all.

SELECT * 
FROM student 
RIGHT OUTER JOIN school 
ON student.school_id = school.school_id;

 – FULL OUTER JOIN

Join table A and table B, and extract all records that match the join condition and all records that did not match the join condition.

Example of statement：
※ “*” means all.

SELECT * 
FROM student 
FULL OUTER JOIN school 
ON student.school_id = school.school_id;

• Supplementary items

The SQL JOINs that I actually used in the work site is INNER JOIN and LEFT OUTER JOIN. My senior in work site also said that they do not use much other than that.

• Other sample

Retrieve a value from join result by using WITH clause and AS clause.

WITH name AS (
   SELECT *
   FROM student
   INNER JOIN school 
   ON student.school_id = school.school_id
)
SELECT student_id, student_name, school_name
FROM name
WHERE
student_id = '1';

20180908