Natural join - sql, PL-SQL Programming

Natural Join - SQL

In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example.

Example: Joining IS_CALLED and IS_ENROLLED_ON in original SQL

SELECT IC.StudentId, Name, CourseId

FROM IS_CALLED AS IC, IS_ENROLLED_ON AS IE

WHERE IC.StudentId = IE.StudentId

866_Joining in SQL.png

Explanation

  • The FROM clause now has two elements. When there are two elements, t1 and t2, the result is equivalent to t1 CROSS JOIN t2, which is SQL's counterpart of t1 TIMES t2 in Tutorial D. However, TIMES requires its operands to have disjoint headings, whereas CROSS JOIN is defined for all pairs of SQL tables. When t1 and t2 each have a column named c, the result has two columns named c. In general, when t1 has m columns named c and t2 has n, t1 CROSS JOIN t2 has m+n columns named c.
  • Following the FROM clause is a WHERE clause, denoting an invocation of the operator WHERE. The operands are the table resulting from the FROM clause and the condition following the word WHERE. SQL's WHERE operator is equivalent to Tutorial D's operator of the same name when its table operand represents a relation.
  • The result of the FROM clause has two columns of the same name, StudentId. The condition specified in the WHERE clause uses range variables, IC and IE, to distinguish between these two columns. The distinction is possible here, thanks to the fact that the same column name isn't used more than once in either of the two operand tables (as we shall see later, that is a condition that does not always apply, even though the same column name cannot be used more than once in a base table).
  • The range variables are defined in the FROM clause alongside the table expressions to which they apply. The key word AS separating the table expression from the range variable name is optional. If the table expression consists of just a table name, unaccompanied by a range variable, then that table name serves also as a range variable name.
  • A range variable is so-called because it is considered to "range over" each element in turn of a collection, the collection in the example at hand being the rows of a table. Note carefully that although the expression IE.StudentId is a column reference, it is not a column name. It references a particular column named StudentId. The prefix "IE." is required because without it the column reference would be ambiguous.
Posted Date: 1/18/2013 4:46:02 AM | Location : United States







Related Discussions:- Natural join - sql, Assignment Help, Ask Question on Natural join - sql, Get Answer, Expert's Help, Natural join - sql Discussions

Write discussion on Natural join - sql
Your posts are moderated
Related Questions
PPD , CPA, Filesharing Site Project Description: This is very easy I need a PPD , CPA, Filesharing Site. [PPD] stands for (pay per download) example hotsharecash [Files

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

Redeclaring Predefined Exceptions Keep in mind that, the PL/SQL declares predefined exceptions globally in the package STANDARD; Therefore you need not declare them yourself.

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Synonyms You can create the synonyms to provide location transparency for the remote schema objects like tables, views, sequences, stand-alone subprograms, and packages. Though,

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Order of Evaluation When you do not use the parentheses to specify the order of evaluation, the operator precedence determine the order. Now compare the expressions below: NOT