SubQuery with EXISTS and NOT EXISTS in PHP

 SELECT with SubQuery Syntax :

SELECT columns FROM table1 WHERE column = (SELECT columns FROM table2 WHERE condition);

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.


If we want to select records from "personal" table who belong to Delhi from "city" table using SubQuery then we can use below shown sql query :

SELECT name FROM personal WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi");

If we want to select records from "personal" table who belong to Delhi and Agra from "city" table using SubQuery then we can use below shown sql query :

SELECT name FROM personal WHERE city IN (SELECT cid FROM city WHERE cityname IN ("Delhi","Agra"));


SELECT with EXISTS Syntax :

SELECT columns FROM table1 WHERE EXISTS(SELECT columns FROM table2 WHERE condition);

NOTE : If any Single Record Exists in Child command then Parent command show results.


SELECT with NOT EXISTS Syntax :

SELECT columns FROM table1 WHERE NOT EXISTS(SELECT columns FROM table2 WHERE condition);

NOTE : If not any Single Record Exists in Child command then Parent command show results.

If we want to apply EXISTS in above query then we can use below shown sql query :

SELECT name FROM personal WHERE city EXISTS (SELECT cid FROM city WHERE cityname IN ("Delhi"));


If we want to apply NOT EXISTS in above query then we can use below shown sql query :

SELECT name FROM personal WHERE city NOT EXISTS (SELECT cid FROM city WHERE cityname IN ("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