UNION and UNION ALL in PHP

 In "personal" table, there are 6 columns named id, name, percentage, age, gender, city.

In "personal" table, column name "id" is PRIMARY KEY and column name "city" is FOREIGN KEY.


In "courses" table, there are 2 columns named course_id and course_name.

In "courses" table, column name "course_id" is PRIMARY KEY.


In "city" table, there are 2 columns named cid and cityname.

In "city" table, column name "cid" is PRIMARY KEY.


In "lecturers" table, there are 6 columns named id, name, percentage, age, gender, id_of_personal.

In "lecturers" table, column name "id" is PRIMARY KEY and column name "id_of_personal" is FOREIGN KEY.


UNION and UNION ALL Syntax :

SELECT column1, column2 FROM table1 UNION/UNION ALL SELECT column1, column2 FROM table2;


RULES :

1) Each SELECT statement within UNION must have the same number of columns.

2) The columns must also have similar data types.

3) The columns in each SELECT statement must also be in the same order.


If we want to UNION both tables named "personal" and "lecturers" then we can use  below shown sql query :

SELECT * FROM personal UNION SELECT * FROM lecturers;

Using above query, when we perform above query then duplicate entries will also appear because we have selected all columns using "*" Operator in (SELECT * FROM).

If we want to remove duplicate "name"  entries in both columns then we have to specify column name as shown below sql query :

SELECT name FROM personal UNION SELECT name FROM lecturers;


If we want to specify any condition in above query then we can use below shown sql query :

SELECT name, age, gender FROM personal WHERE gender = "F" UNION ALL SELECT name, age, gender FROM lecturers WHERE gender = "M";


If we want to select any particular record based on any specific condition then we can use below shown sql query :

SELECT name, age FROM personal WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi") UNION ALL SELECT name, age FROM lecturers WHERE id_of_personal = (SELECT cid FROM city WHERE cityname = "Delhi") ;


If we want to select any particular record from multiple tables using UNION ALL then we can use below shown sql query :

SELECT p.name, p.age, c.cityname FROM personal p INNER JOIN city c ON p.city = c.cid WHERE c.cityname = "Delhi" UNION ALL SELECT l.name, l.age, ci.cityname FROM lecturers l INNER JOIN city ci ON l.id_of_personal = ci.cid WHERE ci.cityname = "Delhi";

Comments

Post a Comment

Popular posts from this blog

GROUP BY Clause and HAVING Clause in PHP

GROUP BY :  The GROUP BY clause is used in conjunction with the SELECT Statement and Aggregate functions to group rows together by common column values. SELECT with GROUP BY Syntax : SELECT columns FROM table_name WHERE condition GROUP BY column_name(s); (Here above "condition" is Optional in above query). In "personal" table, there are 6 columns named id, name, percentage, age, gender, city. In "personal" table, column name "id" is PRIMARY KEY and column name "city" is FOREIGN KEY. In "courses" table, there are 2 columns named course_id and course_name. In "courses" table, column name "course_id" is PRIMARY KEY. In "city" table, there are 2 columns named cid and cityname. In "city" table, column name "cid" is PRIMARY KEY. SELECT with GROUP BY with Two Tables Syntax : SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name WHERE condition GROUP B...
Read more

Method Overriding in Traits in PHP

Image
  <?php class Base {     public function abc ()     {         echo "ABC method from Base class." ;     } } //The trait method(Here, abc() Method) will get priority compared to Parent class Method(Here, abc() Method) trait Test {     public function abc ()     {         echo "ABC method from test trait." ;     } } class Child extends Base {     use Test ;     public function abc ()     {         echo "ABC method from Child class." ;     } } $obj = new Child (); $obj -> abc ();
Read more

Mysqli database Connection and Display Table Data from Database

  <?php $servername = "localhost" ; $username = "root" ; $password = "" ; $dbname = "heer" ; $conn = new mysqli ( $servername , $username , $password , $dbname ); if ( $conn -> connect_error ) {     die ( "Connection Failed : " . $conn -> connect_error ); } $sql = " SELECT * FROM student " ; $result = $conn -> query ( $sql ); if ( $result -> num_rows > 0 ) {     while ( $row = $result -> fetch_assoc ())     {         echo "<pre>" ;         echo "Id: { $row ["id"]} - Name: { $row ["name"]} - Age: { $row ["age"]} - Phone No: { $row ["phone"]} - City: { $row ["city"]}" ;         echo "</pre>" ;     } } else {     echo "No result fount" ; } $conn -> close (); //  OUTPUT: //Id: 0 - Name: Raj - Age: 25 - Phone No: 9854233621 - City: Ahmedabad // Id: 1 - Name: Amit - Age: 22 - Phone No: 8533...
Read more