Natural join - sql, PL-SQL Programming

Assignment Help:

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.

Related Discussions:- Natural join - sql

Using exception_init - user-defined exceptions, Using EXCEPTION_INIT T...

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Anatomy of a command, Anatomy of a Command Figure, showing a simple S...

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Character types in pl/sql, Character Types The Character types allow yo...

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

Use of count in sql, Use of COUNT in SQL It describes and discusses va...

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

%rowcount - implicit cursor attributes, %ROWCOUNT The %ROWCOUNT yields...

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Between operator-comparison operators, BETWEEN Operator The operator BET...

BETWEEN Operator The operator BETWEEN, tests whether the value lies in a specified series. That means "greater than or equivalent to low value and less than or equivalent to hig

%isopen - implicit cursor attributes, %ISOPEN The Oracle closes the SQ...

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Use serially reusable packages - performance of application, Use Serially R...

Use Serially Reusable Packages To help you to manage the use of memory, the PL/SQL gives the pragma SERIALLY_ REUSABLE that mark some packages as serially reusable . So mark

Effect of anonymous columns, Effect of Anonymous Columns Now, recall t...

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Use the pls_integer datatype - performance of application, Use the PLS_INTE...

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd