/SQL UNION OPERATOR
SQL UNION OPERATOR

SQL UNION OPERATOR

SQL UNION OPERATOR
/**
@author : Shubham Maurya,
Email id : maurya.shubham5@gmail.com
**/

Hi all , Welcome to shubhammaurya.com , Today we are going to discuss ,
How to use union operator

SQL UNION OPERATOR

UNION OPERATOR is used to join two or more SELECT statements

  1. In UNION each SELECT statement must have the same number of columns
  2. The columns name must also have similar in their data types
  3. All columns in every SELECT statement must in the same order
UNION SYNTAX
SELECT column_name/column_name(s) FROM table_1 UNION SELECT column_name/column_name(s) FROM table_2

UNION does not select duplicate values, it selects only distinct values

UNION ALL SYNTAX
SELECT column_name/column_name(s) FROM table_1 UNION ALL SELECT column_name/column_name(s) FROM table_2

UNION ALL Selects all duplicate values

 

LET START
SQL UNION OPERATOR

So, To start first make a file in notepad and save it as union.sql and paste the below code.(Database making)

-- phpMyAdmin SQL Dump
-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 12, 2017 at 08:05 AM
-- Server version: 10.1.21-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `union`
--

-- --------------------------------------------------------

--
-- Table structure for table `table_2`
--

CREATE TABLE `table_2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table_2`
--

INSERT INTO `table_2` (`id`, `name`, `email`) VALUES
(1, 'shubham', 'maurya.shubham5@gmail.com'),
(2, 'shubham', 'maurya.shubham5@gmail.com');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `table_2`
--
ALTER TABLE `table_2`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `table_2`
--
ALTER TABLE `table_2`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Second make a file in notepad and save it as dbconfig.php and paste the below code.

<?php

/**
@author : Shubham Maurya,
Email id : maurya.shubham5@gmail.com
**/

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "union";

 try
 {
     $DBcon = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass); 
     $DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // echo "Done..";
 }
 catch(PDOException $e)
 {
     echo "ERROR : ".$e->getMessage();
 }
?>

Third make a file in notepad and save it as index.php and paste the below code.

<?php

/**
@author : Shubham Maurya,
Email id : maurya.shubham5@gmail.com
**/

require_once 'dbconfig.php';

$email="maurya.shubham5@gmail.com";


try 
{
	// UNION
	$stmt=$DBcon->prepare("SELECT name FROM table_1 UNION  SELECT name FROM table_2  ORDER BY name");
	$stmt->execute();
    
    if($stmt->rowcount()>0)
{
	echo "UNION<br><br>";
	while ($row=$stmt->FETCH(PDO::FETCH_ASSOC))
	{
		
		echo "Name : ".$row['name'].'<br>';
	}
}

	// UNION ALL
	$stmt1=$DBcon->prepare("SELECT name FROM table_1 UNION ALL SELECT name FROM table_2  ORDER BY name");
	$stmt1->execute();
    
    if($stmt1->rowcount()>0)
{
	echo "<br>";
	echo "UNION ALL<br> <br>";
	while ($row1=$stmt1->FETCH(PDO::FETCH_ASSOC))
	{
		
		echo "Name : ".$row1['name'].'<br>';
	}
}

	// UNION USING WHERE CLAUSE
	$stmt2=$DBcon->prepare("SELECT name,email FROM table_1 UNION  SELECT name,email FROM table_2 WHERE email=:email");
	$stmt2->execute(array(':email' =>'maurya.shubham5@gmail.com'));
    
    if($stmt2->rowcount()>0)
{
	echo "<br>";
	echo "UNION USING WHERE CLAUSE<br> ";
	while ($row2=$stmt2->FETCH(PDO::FETCH_ASSOC))
	{
		
		echo "<br>name : ".$row2['name'].'<br>';
		echo "email : ".$row2['email'].'<br>';
	}
}

// UNION ALL USING WHERE CLAUSE
	$stmt2=$DBcon->prepare("SELECT name,email FROM table_1 UNION ALL SELECT name,email FROM table_2 WHERE email=:email");
	$stmt2->execute(array(':email' =>'maurya.shubham5@gmail.com'));
    
    if($stmt2->rowcount()>0)
{
	echo "<br>";
	echo "UNION USING WHERE CLAUSE<br> ";
	while ($row2=$stmt2->FETCH(PDO::FETCH_ASSOC))
	{
		
		echo "<br>name : ".$row2['name'].'<br>';
		echo "email : ".$row2['email'].'<br>';
	}
}

} 
catch (PDOException $e) 
{
	 echo $e->getMessage();
}



?>

Find Code at github : click here

Comment Below, If any problem occurs.

STAY CONNECTED FOR MORE

Hi , My Name is Shubham Maurya and i am currently working as a Project Manager.