Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 241 >> Next


In some cases, a search condition is needed in the ON clause. For example, to list all courses offered as well as the course requested by employee 236, the following query can be used:

SELECT Offerings.CourseNo, Enrollment.EmpNo FROM Offerings LEFT JOIN Enrollment ON Offerings.CourseNo = Enrollment.CourseNo AND Enrollment.EMpNo=236;

It is recommended that the search condition on an:

• outer table be put in the WHERE clause,

• inner table be put in the ON clause.

Note: In a left outer join, the outer table is the left table, and the inner table is the right table.

There can be joins using the outer join syntax between two tables, a table and a view, and two views.

Join operations can be performed on views containing an aggregate.

In the following example, table CustFile, is joined with view CustProdSales, containing a SUM operation, in order to determine which companies purchased more than $10,000 of item #123:

CREATE VIEW CustProdSales (custno, pcode, sales)

AS SELECT custno, pcode, SUM(sales) FROM SalesHist GROUP BY custno, pcode;

SELECT company_name, sales FROM CustProdSales a,

CustFile b

WHERE a.custno = b.custno AND a.pcode = 123 AND a.sales > 10000;

3-14

Teradata RDBMS for UNIX SQL Reference
Joins: Working with Multiple Tables

Cross Join

Cross Join

SQL allows unconstrained joins; that is, joins in which a WHERE relationship between the tables that are joined is not specified. The result of an unconstrained join is a cross join, also called a Cartesian product because the join is the product of the number of rows in each table that is joined.

If you do not wish to specify a join condition, and thus wish to return a Cartesian product, which is not an outer join of two or more tables, you would write the following SELECT statement:

SELECT . . .

FROM A CROSS B;

Since the reserved words CROSS and JOIN are optional, you could simply write:

SELECT . . .

FROM A,B;

For example, if Table A contained five rows, and table B contained three rows, the Cartesian product is 3 x 5, or 15. An unconstrained join on these tables would result in 15 rows being returned.

Before performing a cross join, you should consider how expensive, with respect to resource consumption, a cross join may be. For example, a cross join of two tables, A and B, each with 1,000 rows would return a joined table of 1 million rows.

Teradata RDBMS for UNIX SQL Reference

3-15
Joins: Working with Multiple Tables

Self-Join

Self-Join

A self-join combines the information from two or more rows of the same table into a single result row, effectively joining the table with itself.

For example, the following query asks the names of employees who have more years of experience than their managers:

SELECT Workers.Name, Workers.YrsExp, Workers.DeptNo, Managers.Name, Managers.YrsExp FROM Employee Workers, Employee Managers WHERE Managers.DeptNo = Workers.DeptNo AND Managers.JobTitle IN ('Manager', 'Vice Pres')

AND Workers.YrsExp > Managers.YrsExp ;

The operation treats the Employee table as if it were two tables; one named Workers, the other named Managers. This is accomplished using the FROM clause.

Because each of these fictitious tables have the same columns (Name, YrsExp, and DeptNo) each column name must be qualified with its table’s alias name, as defined in the FROM clause (for example, “Workers.DeptNo”).

The WHERE clause establishes the following things:

• A key to both tables (DeptNo)

• Which employees belong in the Managers table (first AND)

• Which workers and managers should be listed in the tables (second AND)

The result of the self-join is as follows:

Name YrsExp DeptNo Name YrsExp
Greene W 15 100 Jones M 13
Carter J 20 500 Watson L 8
Smith T 10 700 Watson L 8
Aguilar J 11 600 Regan R 10
Leidner P 13 300 Phan A 12
Russell S 25 300 Phan A 12

3-16

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Lexical Conventions

Chapter 4

Teradata SQL Lexical Conventions

TeradataR DBMS for UNIX SQL Reference
Teradata SQL Lexical Conventions

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Lexicon

About This Chapter

About This Chapter

This chapter explains how to use the Teradata SQL (Structured Query Language) language, a single, unified, non-procedural language that provides capabilities for query, data definition, data modification, and data control on the Teradata RDBMS.

The SQL lexicon consists of:

• Words

• Names

• Delimiters

• Constants

• Operators

• Lexical separators

• Statement separator

• Request terminator

This chapter includes detail on lexicon for Japanese characters.

The characters that comprise this lexicon may be represented on the client system in ASCII, EBCDIC, or other defined character sets. If the client system character data is not ASCII, it is converted by the Teradata RDBMS to an internal form that depends on the character support chosen (European or Japanese), for processing and storage; data returned to the client system is converted to the client’s character set.
Previous << 1 .. 19 20 21 22 23 24 < 25 > 26 27 28 29 30 31 .. 241 >> Next