Differences between intra-database connections, full connections, and left-right connections

We often use joint queries for two tables. Sometimes we use internal connections, full connections, and left and right connections. Is it very complicated? The following is a demonstration of their differences.

First, there are two tables:

 
 

  
  
CREATE TABLE weather  
  
  
(  
  
  
  city character varying(80) NOT NULL,  
  
  
  temp_lo integer NOT NULL,  
  
  
  temp_hi integer NOT NULL,  
  
  
  prcp real,  
  
  
  date date 
  
  
)  
  
  
WITH (  
  
  
  OIDS=FALSE 
  
  
);  
  
  
ALTER TABLE weather OWNER TO postgres;  
  
  
-----------------------------------------------------------  
  
  
 
  
  
REATE TABLE cities  
  
  
(  
  
  
  "name" character varying(80) NOT NULL,  
  
  
  "location" point  
  
  
)  
  
  
WITH (  
  
  
  OIDS=FALSE 
  
  
);  
  
  
ALTER TABLE cities OWNER TO postgres; 
 
 

Let's look at the data.

Next we start the internal connection. You don't need to explain it too much. You can see what's going on in the picture.

As you can see, inner and select * from weather, cities where city = name have the same output, all of which correspond to one-to-one output. I like it!

Next let's take a look at the picture of the full connection and left and right connections.

As you can see, full join means that all the data in the two tables is listed. If there is no data in the table, leave it blank.

I think this is the case when the left and right join is a fully connected subset.) The left join is used as the reference, and the back table does not have data completing control.

After the right join is completed, the table is used as the benchmark. If the former table does not exist, it is null.