In a relation database, data is typically distributed in more than one table. To select complete data, you often need to query data from multiple tables.
Suppose that you have two tables A and B. The table A has a column pka whose value matches with values in the fka column of table B.
To select data from both tables, you use the INNER JOIN clause in the SELECT statement as follows:
```
SELECT
pka,
c1,
pkb,
c2
FROM
A
INNER JOIN B ON pka = fka;
```
To join table A with the table B, you follow these steps:
* First, specify columns from both tables that you want to select data in the SELECT clause.
* Second, specify the main table i.e., table A in the FROM clause.
* Third, specify the second table (table B) in the INNER JOIN clause and provide a join condition after the ON keyword.
For each row in the table A, inner join compares the value in the pka column with the value in the fka column of every row in the table B:
* If these values are equal, the inner join creates a new row that contains all columns of both tables and adds it to the result set.
* In case these values are not equal, the inner join just ignores them and moves to the next row.
Most of the time, the tables that you want to join will have columns with the same name e.g., id column like customer_id.
If you reference columns with the same name from different tables in a query, you will get an error. To avoid the error, you need to qualify these columns fully using the following syntax:
```
table_name.column_name
```
In practice, you will use table aliases to assign the joined tables short names to make the query more readable.
```
SELECT
customer.customer_id,
first_name,
last_name,
amount,
payment_date
FROM
customer
INNER JOIN payment
ON payment.customer_id = customer.customer_id
ORDER BY payment_date;
```