loading..
Ðóññêèé    English
12:21

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

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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.