คืนค่าการตั้งค่าทั้งหมด
คุณแน่ใจว่าต้องการคืนค่าการตั้งค่าทั้งหมด ?
ลำดับตอนที่ #8 : SQL III - Mutiple Tables
The
countries table
Combining
Tables
Combining
Tables
How Tables are Combined
In SQL, foreign keys and primary keys are used to link tables. By specifying a primary
key in another table with a foreign key, you can link the tables together.
Benefits of Combining Tables
Linking two tables has various advantages over
combining columns. One advantage is that it makes managing data easier, as
shown in the example below.
Combining
Tables
JOIN
What is JOIN?
JOIN
is used to combine multiple tables into one. Conditions for a join are
specified with ON.
You can then get data from the resulting table just as you would with a single
table. Below is an example of how to join two tables, table_b and table_b.
Join
Conditions
To write a join condition, use the syntax: ON table_a.column_name = table_b.column_name. In
the example below on the right, the country_id of the players table and the id
of the countries table are used for the join.
Order
of Execution for JOIN
As shown in the example below, when an SQL
statement contains a JOIN clause, the JOIN is performed first. Next, the SELECT
is performed on the joined table.
Getting
Data with JOIN
Specifying Columns with Multiple Tables
If the same column name exists in more than one
table, it must be specified like so: table_name.column_name.
Below is an example in which a name
column exists in multiple tables.
The syntax table_name.column_name
can also be in a WHERE clause. When working with multiple tables, it's
important to remember that the same column names may exist in different tables
as well.
Overall
Order of Execution
Let's check the overall order in which the various
SQL statement parts will be performed. Note that FROM & JOIN are performed
first because the table must be constructed before SQL searches can be
performed.
The
teams Table
The
teams Table
teams table
Including NULL Rows
When using JOIN, the operation is executed based
on the table specified with FROM. However, rows with a NULL foreign key value, as shown in the
example below, will not be displayed in executed results.
Comparisons
with NULL
LEFT JOIN
Using LEFT JOIN, you can get all the records of the table specified
in the FROM clause. A rows with a NULL foreign key value will also be displayed
in the executed results as NULL.
Combining
3 or More Tables
Combining 3 Tables
JOIN with Multiple Tables
A JOIN is a single SQL statement that can be used
more than once. Note that even if you use JOIN more than once, though, you only
need to write FROM once.
Applying
What You've Learned
ความคิดเห็น