Skip to main content

JOINs in MySQL

JOIN's is used to query data from two or more than two table based on relationship between certain columns in these tables.

Tables in Database are related to each other with some constraints like (Primary Key & Foreign Key)

Primary Key :- Column with unique value for each row that is not value matching to each other in the column.

Foreign Key :- Two tables are mapped through concept of primary and foreign key, that means primary key of one table is used in other table as foreign key.
fig : Primary Key and Foreign Key Example

Query to create the above two tables.


CREATE TABLE `project_mst` (
  `PK_PROJECT_ID` varchar(10) NOT NULL,
  `PROJECT_NAME` varchar(100) default NULL,
  `PROJECT_DURATION` varchar(50) default NULL,
  `FK_MANAGER_ID` varchar(10) default NULL,
  PRIMARY KEY  (`PK_PROJECT_ID`)
)

CREATE TABLE `manager_mst` (
  `PK_MANAGER_ID` varchar(10) NOT NULL,
  `MANAGER_NAME` varchar(100) default NULL,
  PRIMARY KEY  (`PK_MANAGER_ID`)
)

1. INNER JOIN

INNER JOIN returns rows when there is at least one match in both the tables.

Syntax :

SELECT * FROM table1 LEFT JOIN table2 on table1.colunm_name = table2.colunm_name

Example1 :

SELECT * FROM manager_mst INNER JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID

Example2 :

SELECT * FROM manager_mst , project_mst where PK_MANAGER_ID = FK_MANAGER_ID

2. LEFT JOIN

Return all rows from the left table, in conjunction with rows matching in right table and if there are no matches values in the right table it returns null value.

Syntax :

SELECT * FROM table1 LEFT JOIN table2 on table1.colunm_name = table2.colunm_name

Example :

SELECT * FROM manager_mst LEFT JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID

3. RIGHT JOIN

Return all rows from the right table, in conjunction with rows matching in left table and if there are no matches values in the left table it returns null value.

Syntax :

SELECT * FROM table1 RIGHT JOIN table2 on table1.colunm_name = table2.colunm_name

Example :

SELECT * FROM manager_mst RIGHT JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID

4. FULL JOIN

FULL JOIN return rows when there is a match in one of the tables.

Syntax :

SELECT * FROM table1 FULL JOIN table2 on table1.colunm_name = table2.colunm_name

Example :

SELECT * FROM manager_mst FULL JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID

5. LEFT OUTER JOIN

LEFT OUTER JOIN returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

Syntax :

SELECT * FROM table1 LEFT OUTER JOIN table2 on table1.colunm_name = table2.colunm_name

Example :

SELECT * FROM manager_mst LEFT OUTER JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID

6. RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all the rows from the right table in conjunction with the matching rows from the left table.

Syntax :

SELECT * FROM table1 RIGHT OUTER JOIN table2 on table1 .column_name = table2 .column_name

Example :

SELECT * FROM manager_mst RIGHT OUTER JOIN project_mst on PK_MANAGER_ID = FK_MANAGER_ID


I hope that above given example gives you a better understanding of JOINs and helps you write more efficient SQL queries.
Labels:

Comments

Post a Comment

Popular posts from this blog

Creating Pdf in multiple Languages in Java

Creating Pdf in multiple Languages in Java This code demonstrate how to create pdf in multiple languages in java by using ITEXT pdf creator. To use this sample code download itext.jar To display different locale on pdf you need to download font known as Arialuni.ttf and added to the project Most important thing is the encoding part set encoding to "Identity-H" (String encoding = "Identity-H";) as well as create font for the same to take effect in pdf create font (Font fontNormal = FontFactory.getFont(("c:/windows/fonts/arialuni.ttf"), encoding,BaseFont.EMBEDDED, 8, Font.NORMAL);) You can download the source code from here import java.io.FileOutputStream; import com.lowagie.text.Chunk; import com.lowagie.text.Document; import com.lowagie.text.Font; import com.lowagie.text.FontFactory; import com.lowagie.text.HeaderFooter; import com.lowagie.text.Paragraph; import com.lowagie.text.Phrase; import com.lowagie.text.pdf....
4 comments
Read more

Basic Java Interview Questions

1. Why java does not support multiple inheritance ? Answer :- To say why java doesn't support inheritance directly like c++ should be the diamond problem faced by c++,(Diamond problem is an ambiguity that arises when two classes B and C inherit from A, and class D inherits from both B and C. If a method in D calls a method defined in A (and does not override it), and B and C have overridden this method differently, then via which class does it inherit: B, or C?) 2. JVM crash is an Error or Exception.? Answer:- JVM crash is an Error as a normal developer as we cannot handle it, but its exception for JVM developer as they can fix it. 3. Print “Hello World!” Without Main Method in Java? Answer:- public class Testing {    static    {        System.out.println("Hello World");    }    public static void main(String[] args)    {    } } ...
Post a Comment
Read more

Collections in Java

Collections in Java Introduction to Collections Framework The Collections Framework provides a well defined set of interfaces and classes for storing and manipulating the groups of data into a single unit. Introduction to Collections Framework Collections Framework: The Collections Framework provides a well-designed set of interfaces and classes for storing and manipulating the groups of data into a single unit. The collections framework is a unified architecture which is used to represent and manipulate collections. The framework allows the collections to get manipulated independently, additionally it reduces the programming efforts and increases performance. It includes implementation of interfaces and algorithms. Basically it is a unified architecture that consists the following collections: Interfaces: These are the abstract data types that represent collections. With the help of interfaces we manipulate collections independently. A hierarchy is generally formed ...
Post a Comment
Read more