Mastering the connection to SQL

1. Understanding the SQL connection

The join in SQL is used to concatenate rows of multiple tables, here are 2 tables, where the UserID in the MyBook table is a foreign key, and the UserID in the MyUser table is the primary key.

First, let's look at a simple inner-connected SQL, as shown in the results.

Select Mybook.bookname,myuser.username,myuser.userage from MyUser inner joins MyBook on Mybook.userid=myuser.userid;

Inner JOIN is the default connection in SQL, meaning it is the same as join. The INNER JOIN keyword returns rows if there is at least one match in the table, and if the userid of the MyBook table does not match in the MyUser tables, the result will be nothing. In fact, the so-called connection is based on a column of 2 tables to make 2 tables together, in fact, this is like a view, and then from the view to get the columns to be displayed.

2. External connection

The outer joins are left-connected, right-connected, and fully connected. We can execute these 3 SQL statements together to see their results.

SelectMybook.bookname,myuser.username,myuser.userage fromMyUserInner JoinMyBook onMybook.userid=Myuser.userid;SelectMybook.bookname,myuser.username,myuser.userage fromMyUser Left JoinMyBook onMybook.userid=Myuser.userid;SelectMybook.bookname,myuser.username,myuser.userage fromMyUser Right JoinMyBook onMybook.userid=Myuser.userid;SelectMybook.bookname,myuser.username,myuser.userage fromMyUser Full JoinMyBook onMybook.userid=Myuser.userid;

The difference between these joins can be clearly observed from the result, and the first connection will create a table that resembles a view. For a left connection, if there are rows that match unsuccessfully, the row is still returned and the right table shows null. The right connection is just the opposite of the left connection, and the data on the left side of the line is returned without a match, except that the left column shows null, and the full connection is the result of a combination of left and right connections.

Mastering the connection to SQL