SQL JOIN

Category: SQL Commands

Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world SQL usage you will often need to get data from multiple tables in a single query. Oracle provides the facility to retrieve the data from multiple tables with the help of joins. It is perhaps a wonderful feature of SQL that permit to retrieve the data from multiple users.

 

CROSS JOIN

Category: SQL Commands

A cross join, or product provides the foundation upon which all types of inner joins operate. A cross join returns the Cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to true or join-condition is absent in statement. This command is introduced for Oracle9i. If A and B are two sets, then the cross join is written as A x B.

 

FULL OUTER JOIN

Category: SQL Commands

To retrieve the Employee name, job, salary, deptno, dname from EMP, DEPT table. It will return all information regarding the employees and the department information. This is Full Outer Join's Example.

 

LEFT OUTER JOIN

Category: SQL Commands

The result of a left outer join (or simply left join) for table A and B always contains records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result-but with NULL in each column from B.

 

SQL INDEXES

Category: SQL Commands

When the user fires a SELECT statement to search for a particular record, the oracle engine must first locate the table on the hard disk. The Oracle engine reads the information and locates the starting location of a table's record on the current storage media.

 

SQL INNER JOIN

Category: SQL Commands

In SQL inner joins are also called simple joins or equijoin. We are now ready to present a SELECT statement with what is called an inner join:

 

OUTER JOIN

Category: SQL Commands

It is important to note that the INNER JOIN only returns data where a match is found. While using the inner join we have seen that if there exists certain records in one table. Which doesn't have corresponding values in the second table then those rows will not be selected.

 

COMPOSITE INDEX

Category: SQL Commands

When an index is created by using multiple columns then it is known as Composite index .

• Create an index on the NAME AND SALARY columns of the employees table.

 

SELF JOIN

Category: SQL Commands

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together.

 

SQL CREATE INDEX

Category: SQL Commands

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

 

Page 1 of 2



Dinesh ThakurDinesh Thakur is a Columinist and designer with strong passion and founder of Computer Notes. if you have any ideas or any request please get @me on Google+
linkedin FaceBook Twitter Google Plus