SQL step-by-Step join clause

Join clause

I don't know if you've found it. We can only do this from one table when we use the SELECT statement to retrieve it. What if you want to retrieve it from two or more tables? Fortunately we can use a very useful feature of SQL and relational database systems, that is, "Join". For the sake of simplicity, "Join" is actually a relational database system-related stuff. Join allows you to retrieve data from two tables or more tables, using only one SELECT statement. If you have more than one table after the FROM keyword, Join can recognize them in the SQL SELECT statement.

Here's an example:

Select "List-of-columns"

From Table1,table2

WHERE "Search-condition (s)"

"Join"

By demonstrating what happens when you're only working on a table, you can make the "join" easier to explain, so I'm not using join. This single database is also known as "flat Table" (flat). Now that you have a table of databases to retrieve all the customer information and what they bought from your store, here are all the columns of this table:

Each time a new row is inserted into the table, all columns are updated, which results in unnecessary "redundant data." For example, every time Jenny buys something, the following lines are inserted into the table:

One of the best ways to avoid "redundant data" is to have the database have two tables: one to keep track of customers, and the other to keep track of what they buy. There are "customer_info" tables and "purchases" tables:

The "Customer_info" table is:

Customer_number

FirstName

LastName

Address

City

State

Zip

"Purchases" Table is:

Customer_number

Date

Item

Price

Starting now, no matter when the customer does a repeat shopping, only the second table "purchases" needs to be updated. So we cut out the extra data, which means we normalized the database.

If you're careful, you'll find that two tables still have a "cusomer_number" column that is the same. This column contains a separate customer number that will be used to join (connect) two tables. Here's an example of using these two tables, and if you want to search for a customer's name and what they're buying, you can use the following statement:

SELECT Customer_info.firstname, Customer_info.lastname, Purchases.item

From Customer_info, purchases

WHERE customer_info.customer_number = Purchases.customer_number;

Special "Join" for "Inner join" or "equijoin", this is one of the most common "join" type, later we often use to or see.

Note that each column is always preceded by a table name, which is not required. This is a good exercise to help you clarify the understanding of the table following the column. If two tables have the same column, it is necessary. I recommend that when you use join, it's best to add the table name after all the columns.

Note that the syntax described above will work in most database systems, as is the tutorial. But you will find that the above statement does not work, please check it carefully.

Of course you can try to modify the above code, you can use the join (ANSI SQL-92 Syntax specification of the INNER join):

SELECT Customer_info.firstname, Customer_info.lastname, Purchases.item

From Customer_info INNER JOIN purchases

on customer_info.customer_number = Purchases.customer_number;

Let me give you another example:

SELECT Employee_info.employeeid, Employee_info.lastname, employee_sales.comission

From Employee_info, Employee_sales

WHERE Employee_info.employeeid = Employee_sales.employeeid;

This example will select from the Employee_info and Employee_sales tables when the EmployeeID of the Employee_info table is equal to Employee_sales EmployeeID, The Comission values in the LastName and Employee_sales tables in the Emplyee_info table.

From the above example, we can find that the statement using Jion is concise. Since there is such a kind of, we do not do it?