loading..
   English
17:44

Equi-joins page 2

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:

  1. 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")

  1. SELECT * FROM Income JOIN Outcome USING(date)
  2. 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

  1. SELECT * FROM Income JOIN Outcome USING(date, point)
  2. 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

  1. 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

  1. SELECT * FROM Income NATURAL JOIN Outcome;

To demonstrate the output in the last example, let's use the left join

  1. SELECT * FROM Income LEFT JOIN Outcome USING(date, point, code)
  2. WHERE MONTH(date) >= 4;

code point date inc out
6 1 2001-04-13 00:00:00 5000.00 NULL
7 1 2001-05-11 00:00:00 4500.00 NULL
10 1 2001-04-13 00:00:00 5000.00 NULL
12 3 2001-09-13 00:00:00 1350.00 NULL
13 3 2001-09-13 00:00:00 1750.00 NULL

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100