Defferent types Joins


INNER JOIN:
              The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax:

The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
 
Example:  
SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     INNER JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

LEFT JOIN:      

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax:

The basic syntax of LEFT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1 
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Here given condition could be any given expression based on your requirement.

Example:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
 

RIGHT JOIN:
 
The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax:

The basic syntax of RIGHT JOIN is as follows:
SELECT table1.column1, table2.column2... FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

Example:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
 

CARTESIAN JOIN: 

 
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of CARTESIAN JOIN or CROSS JOIN is as follows:
SELECT table1.column1, table2.column2... FROM table1, table2 [, table3 ]

Example:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;

 

 

 

 

 

Comments

Post a Comment

Popular posts from this blog

Classification of Data-Delivery Mechanisms in mobile computing

Image
Data Dissemination and Broadcasting Systems: Classification of Data-Delivery Mechanisms: ·          Push-based mechanisms (publish–subscribe mode) ·          Pull-based mechanisms (on-demand mode) ·          Hybrid mechanisms (hybrid mod                        Push-based Mechanisms: ·          Server pushes data records from a set of distributed computing systems ·          Examples of distributed computing systems advertisers or generators of traffic congestion, weather reports, stock quotes, and news report A server or computing system pushing the data records from a set of distributed systems : Publish–subscribe mode • In which the data pushed as per the subscription for a push service by a user Pushing Algorithm: Select a structure of data records to be pushed • An algorithm provides an adaptable multi-level mechanism that permits data items to be pushed uniformly or none uniformly after structuring them according to their relative impor
Read more

Client Server Computing with Adaptation

Image
Client Server Computing with Adaptation • Two adapters (adaptation software) at a mobile device • An adapter for standard data format for synchronization at the mobile-device • Another adapter for the backend database copy, which is in a different data format for the API at the mobile-device Adaptation: Software to get data in one format or data governed by one protocol and convert it to another format or to data governed by another protocol ·          Used for interchange between standard data formats and data formats for the API ·          IBM WebSphere Everyplace Access (WEA) provides adapters for synchronization objects (for example, XML format synchronization objects) and the objects of API databases (for example, for the PIM APIs)
Read more

Data Dissemination Broadcast-disk Models

Image
Data Dissemination Broadcast-disk Models: ·          Presumed that all the n records to be broadcast are stored on a circular disk from 0° to 360° ·          A hypothetical disk revolves and the angle changes from 0°to 360° ·          The entire N bits in n records get pushed through a hypothetical reading-head over the disk. ·          The head continuously reads each bit of a record just beneath it and broadcasts it instantaneously on the wireless network ·          During next revolution each bit in the records positioned from 0° to 360° broadcast once again in the same sequence as in earlier revolution Number of adaptations and algorithms for broadcast: 1. Circular Multi-disk Model 2. Flat Disk Model 3. Multi-disk Model with repetition rate proportional to priority 4. Skewed disk model Circular multi-disk model • Each block of records pushed with a repetition rate proportional to its hierarchical level Each rotating disk cycle:
Read more