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

Executing executable Jar file from Java Program

Sample code to execute jar file from Java Program. Pre-requisite 1. Need to have executable jar file. 2. Copy the code in your workspace and Run the code. package com.test.reusable.enterprise.service; import java.io.BufferedReader; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import org.apache.commons.lang.exception.ExceptionUtils; public class ExecuteJarFile { public static void main(String[] args) { try { //Method1 to Execute Jar file From Java Program ProcessBuilder pb = new ProcessBuilder("java", "-jar", "HelloWorld.jar"); // command to execute jar file with fileName.jar pb.redirectErrorStream(true); pb.directory(new File("C:/www/")); // Directory path where your jar file is placed. Process p = pb.start();
Post a Comment
Read more

Remove element from Array Javascript

Remove element from Array Javascript ----------------------------------------------------------------------- const array = ["test1", "test2", "test3", "test4"]; console.log(array); const index = array.indexOf("test2"); if (index > -1) {   array.splice(index, 1); } // **** array = ["test1", "test3", "test4"] console.log(array); 
Post a Comment
Read more