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

Cursor variables in pl sql, Cursor Variables:   To execute the multi-...

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Ending transactions, Ending Transactions A good quality programming pr...

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Lob types in pl/sql, LOB Types The large object (LOB) datatypes like BF...

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

Read-only operator (+) - sql, Read-Only Operator (+) - SQL The term r...

Read-Only Operator (+) - SQL The term read-only operator to the mathematical term function. Here I just need to add that the SQL standard reserves the term function for read-

%found - explicit cursor attributes, %FOUND Subsequent to a cursor or ...

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

Using operator deref - manipulating objects in pl sql, Using Operator DEREF...

Using Operator DEREF: You cannot navigate through refs within the PL/SQL procedural statements. Rather than, you should use the operator DEREF in the SQL statement. The DEREF

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Introduction to SQl and DQL, which operation is used if we are interested i...

which operation is used if we are interested in only certain columns of a table?

I need sql - wp sql expert, I need SQL , WP SQL Expert Project Descripti...

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

Build a purchases report that matches the general ledger, Great Plains (Mic...

Great Plains (Microsoft Dynamics) Purchases Report Project Description: I want to build a purchases report that matches the General Ledger. presently, when I join the PM20

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