If you want to perform an equi-join not on all columns with matching names, but only on their part, then we can use the connection USING:
Table_1 < JOIN type > JOIN Table_2 USING(< COLUMN list >)
The list of columns contains those columns by which the equi-join is performed. Accordingly,this list can contain only those of the columns whose names coincide in both joining tables.
The list of columns contains those columns by which the equi-join is performed. Accordingly,this list can contain only those of the columns whose names coincide in both joining tables.
The join of rows from the Income and Outcome tables by the equality of values in the date column (database "Recycled firm")
SELECT * FROM Income JOIN Outcome USING(date)
WHERE MONTH(date) >= 4;
date
code
point
inc
code
point
out
2001-04-13 00:00:00
6
1
5000.00
7
1
4490.00
2001-04-13 00:00:00
10
1
5000.00
7
1
4490.00
2001-05-11 00:00:00
7
1
4500.00
9
1
2530.00
2001-09-13 00:00:00
12
3
1350.00
16
3
1200.00
2001-09-13 00:00:00
13
3
1750.00
16
3
1200.00
2001-09-13 00:00:00
12
3
1350.00
17
3
1500.00
2001-09-13 00:00:00
13
3
1750.00
17
3
1500.00
The join of rows from the Income and Outcome tables by the equality of values in the date and point columns
SELECT * FROM Income JOIN Outcome USING(date, point)
WHERE MONTH(date) >= 4;
point
date
code
inc
code
out
1
2001-04-13 00:00:00
6
5000.00
7
4490.00
1
2001-04-13 00:00:00
10
5000.00
7
4490.00
1
2001-05-11 00:00:00
7
4500.00
9
2530.00
3
2001-09-13 00:00:00
12
1350.00
16
1200.00
3
2001-09-13 00:00:00
13
1750.00
16
1200.00
3
2001-09-13 00:00:00
12
1350.00
17
1500.00
3
2001-09-13 00:00:00
13
1750.00
17
1500.00
The join of rows from the Income and Outcome tables by the equality of values in the date, point, and code columns
SELECT * FROM Income JOIN Outcome USING(date, point, code);
does not return any rows.
This join over all columns with matching names is equivalent to a natural join
SELECT * FROM Income NATURALJOIN Outcome;
To demonstrate the output in the last example, let's use the left join
SELECT * FROM Income LEFTJOIN Outcome USING(date, point, code)
go
