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

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 of any attribute in the table." ➧ ALTER TABLE EMP RENAME COLUMN NAME TO FIRST_NAME; ➧  ALTER TABLE EMP DROP COLUMN HIRE_DATE;
Post a Comment
Read more

Constraints

                                    CONSTRAINTS Constraints is a rule which enforce entry of only valid data in table. Constraints prevent the deletion of a table if there are dependencies. Types of Constraints:-   ➥ PRIMARY KEY= (unique + not null)   ➥ FOREIGN KEY   ➥ NOT NULL   ➥ UNIQUE   ➥ CHECK ➧PRIMARY KEY:- Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.  ➧FOREIGN KEY:- Specifies that the values in the column must correspond to values in a referenced primary key or unique key column. ➧NOT NULL:- Specifies that this column cannot hold NULL values. ➧UNIQUE:- Specifies that values in the column must be unique. Column can contain NULL value. ➧CHECK:- Specifies Domain for Column values. A CONSTRAINT can be one of the following: * A column-level constraint * A table-level constraint ➮ Primary Key: SQL> CREATE TABLE EMP(ID NUMBER(3) PRIMAR
Post a Comment
Read more

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  SUM(bal_due) from client_master;         ➥ NUMERIC FUNCTIONS:- ABS(number) Returns the absolute positive value of an expression. Syntax : ABS(expression) Example : SELECT ABS(-1.0), ABS(0.0), ABS(1.0) Output : 1.0   .0   1.0 CEIL(numb
Post a Comment
Read more