MYSQL Tutorial Part 7

-

 JOINS:

    A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

  • INNER JOIN: Returns records that have matching values in both tables.

    SELECT column_names FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table.

    SELECT column_names FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;


  • RIGHT JOIN: Returns all records from the right table and the matched records from the left table.

    SELECT column_names FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;


  • CROSS JOIN: Returns all records from both tables.

    SELECT column_names FROM table1 CROSS JOIN table2;
    Example
         SELECT Students.name, Departments.DeptHOD, Departments.name FROM Students INNER     JOIN Departments ON Students.department=Departments.name;

SELF JOIN:

  • A SELF JOIN is a regular join, but the table is joined with itself.
  • Using INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN combines the table with another table but SELF JOIN is used to combine the table with the same table.

    Syntax

        SELECT column_names FROM table1 T1, table1 T2 WHERE condition;


    Example
        SELECT stud1.name AS Student1, stud2.name AS Student2, stud1.district FROM Students stud1, Students stud2 WHERE stud1.rollno <> stud2.rollno AND stud1.district = stud2.district ORDER BY stud1.district;

Note: [<> operator is used for not equal condition]

UNION:

    The UNION operator is used to combine the result set of two or more SELECT statements.

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order

    Syntax

        SELECT column_names FROM table1

        UNION

        SELECT column_names FROM table2;

    Example

        SELECT department FROM Students

        UNION

        SELECT department FROM Students2

        ORDER BY department;

UNION ALL:

  • The UNION operator selects only distinct values by default. 
  • To allow duplicate values, use UNION ALL.

    Syntax

        SELECT column_names FROM table1

        UNION ALL

        SELECT column_names FROM table2;

    Example

        SELECT department FROM Students

        UNION ALL

        SELECT department FROM Students2

        ORDER BY department;




                                                Thanks for choosing our blog 😊. 
                                        Will work with an example in the next part.

Tutorial Topics:
Introduction  -  Part-1
Database Creation  -  Part-2
Aggregate Functions  - Part-3
Conditions  - Part-4
Constraints  - Part-5
Clauses  -  Part-6
Joins  -  Part-7


Tutorial Topics for examples:
Database Creation -  Part-1
Table Modifications  -  Part-2
Clauses   - Part-3
Operators  - Part-4
Joins  - Part-5

Comments

Post a Comment

Popular posts from this blog

MySQL Tutorial Part 1

-
Image
MYSQL: MySQL is a very popular open-source relational database management system. It is very easy to use. MySQL is one of the most straightforward database technology to learn and use. To add, access, and process data stored in a database. SQL: SQL stands for Structured Query Language SQL is used to communicate with the database. SQL lets you access and manipulate databases SQL keywords are NOT case sensitive: select is the same as SELECT SQL VS MYSQL: SQL MYSQL SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way. RDBMS: RDBMS stands for Relational Database Management System. RDBMS is a program used to maintain a relational ...
Read more

Pandas DataFrame empty

-
Image
HOW TO CHECK IF THE DATAFRAME IS EMPTY OR NOT? Check if the DataFrame is empty or not by using the  DataFrame.empty .  It returns the Boolean value. If the DataFrame is empty, then it returns True . If the DataFrame is not empty, then it returns False . CREATE A EMPTY DATAFRAME: Create an empty dataframe and will check if it is empty or not by using the property DataFrame.empty If it is empty, then it returns True. import  pandas as pd  empty_dataframe = pd.DataFrame() if empty_dataframe.empty:       print ('True') CREATE A NON-EMPTY DATAFRAME: Create a non-empty dataframe and will check if the dataframe is empty or not.  If it is not empty, then it returns False. import  pandas as pd  pd_dataframe= pd.DataFrame( [['Vetri',20],['Vishal',21],['Jack',18]],   columns=['Name', 'Age'] ) if  pd_dataframe .empty is False:      print ('False') Thanks for choosing our blog 😊. 
Read more

Introduction to Java

-
Image
JAVA      James Gosling Invented Java in 1991 and it is developed by Sun Microsystems. But the first version was released in 1995. The current version of Java is Java 17.  Java is an Object Oriented Programming language and it is platform-independent ( Write once and run anywhere ).  Java is easy to compile, and debug and it provides reusability of code that makes development faster.  The following four are important to run, compile and debug the Java application   JDK - Java Development Kit   JRE - Java Runtime Environment  JVM - Java Virtual Machine  Compiler - Java Compiler.    JDK           It is used to provide tools to develop Java applications it comes with Compiler, JRE, and JVM.  COMPILER  Java is the compiler used for compilation.  The compiler will check the program syntax and if it is wrong then throw the error at compile time.   If we don't hav...
Read more