Skip to main content

Joins in SQL

                                          JOINS


➤INNER JOIN:- This join returns when there is at least one match in both the Tables.
Example: SELECT * FROM TABLE1 T1 INNER JOIN TABLE T2 ON T1.COL1=T2.COL1;


➤OUTER JOINS

➧Left Outer Join:- This join returns all the rows from the left table in conjunction with matching rows from the right table.If there are no columns matching in right table it returns, NULL values.
Example: SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON 
T1.COL1=T2.COL1;


 ➧Right Outer Join:- This join returns all the rows from the right table in conjunction with matching rows from the left table.If there are no columns matching in left table, it return NULL values.
Example: SELECT * FROM TABLE1 T1 RIGHT OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1;


➧Full Outer Join:- This join combines left outer join and right outer join.It returns row from  either table when the condition are met and return null values when there is no match.
Exaample: SELECT * FROM TABLE1 T1 FULL OUTER JOIN TABLE2 T2 ON T1.COL1=T2.COL1;


➤CROSS JOIN:- This join is a cartesian join that does not necessitate any condition to join.The result set contain records that are multiplication of records number from both the table.
Example: SELECT * FROM TABLE1 T1 CROSS JOIN TABLE2 T2;

NOTE: Simple join Query-

Example: SELECT * FROM TABLE1 WHERE TABLE1.COL1=TABLE2.COL1;

Labels:

Comments

Post a Comment

Popular posts from this blog

Aggregate And Numeric Functions.

                   Aggregate And Numeric Functions SQL has many built-in functions for performing processing on string or numeric data. ➥ AGGREGATEE FUNCTIONS:-    ➧ Average function : Returns average value of 'n', ignoring null values.         Example: SE LECT AVG(sell_price) from product;      ➧ COUNT  :Counts rows in a specified table or view.        Example: SELECT    COUNT ( * ) FROM products ;     ➧ MIN  : G ets the minimum value in a set of values.         Example: SELECT MIN ( unitsinstock ) FROM   products ;     ➧ MAX  : Gets the maximum value in a set of values.         Example: SELECT    MAX ( unitsinstock ) FROM    products ;     ➧ SUM  :Ca lculates the sum of values       Example:SELECT ...
Post a Comment
Read more

DDL Queries

                        DDL(DATA DEFINITION LANGUAGE)             DDL  refers to "Data Definition Language", a subset of SQL statements that change the structure of the database schema in some way, typically by creating, deleting, or modifying schema objects such as databases, tables, and views.    ➤CREATE ➤ALTER ➤DROP ➤TRUNCATE   ➤RENAME ➥ Create Query:- ➧     CREATE TABLE EMP(EMPID NUMBER(5),NAME VARCHAR2(15), SALARY  NUMBER(7,2),EMAIL VARCHAR(20)); ➧    CREATE TABLE DEPT AS SELECT * FROM DEPARTMENTS; "This Query will Create Table when we want to copy the data of any table into new table.Here old table is DEPARTMENTS. ➥ Alter Query :-  ALTER' COMMAND IS USED TO MODIFY TABLE STRUCTURE. ➧     ALTER TABLE EMP MODIFY EMPID NUMBER(10);  "this will change the data type...
Post a Comment
Read more