Internal and external connections in PostgreSQL to implement operations

Time:2022-5-13

Test data:

City Table:

create table city(id int,name text);
Insert into city values (0, 'Beijing'), (1, 'Xi'an'), (2, 'Tianjin'), (3, 'Shanghai'), (4, 'Harbin'), (5, 'Tibet')

Person table:


create table person(id int,lastname char(20));
insert into person values(0,'Tom'),(2,'Lily'),(3,'Mary'),(5,'Coco');
select * from city;


select * from person;

1: Internal connection:

1.inner join

Inner join returns only rows with equal join fields in two tables

SQL statement:


select * from city inner join person on city.id = person.id;

It can also be written as:


select * from city join person on city.id = person.id;

The results are as follows:

As can be seen from the results, the city is shown in the table id=person. ID, which shows the records that meet this condition.

2: External connection:

1.full outer join

Full outer join returns all the data in the two data sets participating in the connection

SQL statement:


select * from city full outer join person on city.id = person.id;

It can also be written as:


select * from city full join person on city.id = person.id;

The results are as follows:

It can be seen from the results that all the data in the city and person tables are obtained by the external connection

2.left outer join

Left outer join returns records including all records in the left table and those with equal connection fields in the right table

SQL statement:


select * from city left outer join person on city.id = person.id;

It can also be written as:


select * from city left join person on city.id = person.id;

The results are as follows:

From the results, we can see that the results of left outer connection and all outer connection are the same?

We add a row of data to person:


insert into person values(9,'Kiki');

On re execution:


select * from city full join person on city.id = person.id;

The results are as follows:


select * from city left join person on city.id = person.id;

The results are as follows:

Comparing the two results, left join shows all records in the city and records with equal person connection fields

3.right outer join

Right outer join returns records including all records in the right table and those with equal connection fields in the left table

SQL statement:


select * from city right outer join person on city.id = person.id;

It can also be written as


select * from city right join person on city.id = person.id;

The results are as follows:

It can be seen from the results that all records in person are displayed, while the data displayed in city is the same records according to the connection fields

Supplement: PostgreSQL table connection: internal connection, external connection, self connection, cross connection

After searching, they are basically three types: write internal connection, external connection and cross connection, but I found that PostgreSQL also has self connection. You might as well write it down together for a record.

Let’s start with the concept:

Internal connection means that two tables are matched row by row. The contents on the matching are displayed, and those without matching are not displayed.

There are three kinds of external connections: left external connection, right external connection and full external connection.

The left outer connection is based on the left table. All the contents of the left table are displayed. If the right table matches the left table, it will be displayed. Otherwise, it will not be displayed.

The right external connection is based on the right table. All the contents of the right table are displayed. If the left table matches the right table, it will be displayed. Otherwise, it will not be displayed.

Total external connection is based on two tables and displays three parts. One part is the content of internal connection, that is, the matching content of the two tables. One part is the content of the left table but not the right table, and the other part is the content of the left table but not the right table.

Self join is to match the current row of data with other rows in the table row by row.

Cross connection is the easiest. Cartesian product. If the left table has m rows and the right table has n rows, the result is m * n rows.

Specific examples are shown below to help understand.

Here are the contents of the two tables.

mydb=# select * from weather;
   city   | temp_lo | temp_hi | prcp |  date
---------------+---------+---------+------+------------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27
 San Francisco |   43 |   57 |  0 | 1994-11-29
 Hayward    |   37 |   54 |   | 1994-11-29
(3 lines of records)
 
mydb=# select * from cities;
   name   | location
---------------+-----------
 San Francisco | (-194,53)
 London    | (0,51)
(2 lines of records)

There are two ways to write an inner connection:

mydb=# SELECT *
mydb-#   FROM weather, cities
mydb-#   WHERE city = name;
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
(2 lines of records) 
 
mydb=# SELECT *
mydb-#   FROM weather INNER JOIN cities ON (weather.city = cities.name);
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
(2 lines of records)

There are three kinds of external connections: left external connection, right external connection and full external connection.

mydb=# SELECT *
mydb-#   FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
 Hayward    |   37 |   54 |   | 1994-11-29 |        |
(3 lines of records) 
 
mydb=# select * from weather right outer join cities on(weather.city=cities.name);
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
        |     |     |   |      | London    | (0,51)
(3 lines of records) 
 
mydb=# select * from weather full outer join cities on(weather.city=cities.name);
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
 Hayward    |   37 |   54 |   | 1994-11-29 |        |
        |     |     |   |      | London    | (0,51)
(4 lines of records)

Table cross connect:

mydb=# SELECT *
mydb-#   FROM weather, cities;
   city   | temp_lo | temp_hi | prcp |  date  |   name   | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |   46 |   50 | 0.25 | 1994-11-27 | London    | (0,51)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | San Francisco | (-194,53)
 San Francisco |   43 |   57 |  0 | 1994-11-29 | London    | (0,51)
 Hayward    |   37 |   54 |   | 1994-11-29 | San Francisco | (-194,53)
 Hayward    |   37 |   54 |   | 1994-11-29 | London    | (0,51)
(record line 6)

Table self connection:

mydb=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
mydb-#   W2.city, W2.temp_lo AS low, W2.temp_hi AS high
mydb-#   FROM weather W1, weather W2
mydb-#   WHERE W1.temp_lo < W2.temp_lo
mydb-#   AND W1.temp_hi > W2.temp_hi;
   city   | low | high |   city   | low | high
---------------+-----+------+---------------+-----+------
 San Francisco | 43 |  57 | San Francisco | 46 |  50
 Hayward    | 37 |  54 | San Francisco | 46 |  50
(2 lines of records)

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If there are mistakes or not fully considered, please don’t hesitate to comment.