Snippets

sql

JOIN, CROSS JOIN, and INNER JOIN in MySQL

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

join cross-join on-clause

sql

CARTESIAN JOIN or CROSS JOIN

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of CARTESIAN JOIN or CROSS JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Read more: SQL - CARTESIAN or CROSS JOINS

inner-join

sql

INNER JOIN example

Example:

Consider the following two tables,
(a) CUSTOMERS table is as follows:
| ID | NAME     |
+----+----------+
|  1 | Ramesh   |
|  2 | Khilan   |
(b) Another table is ORDERS as follows:
|OID  | DATE  | C_ID | AMOUNT |
+-----+-------+------+--------+
| 102 | 10-08 |    3 |   3000 |
| 100 | 10-08 |    3 |   1500 |
| 101 | 11-20 |    2 |   1560 |
Now, let us join these two tables using INNER JOIN as follows:
SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;
This would produce the following result:
| ID | NAME     | AMOUNT | DATE  |
+----+----------+--------+-------+
|  1 | Ramesh   |   3000 | 10-08 |
|  1 | Ramesh   |   1500 | 10-08 |
|  1 | Ramesh   |   1560 | 11-20 |
|  2 | Khilan   |   3000 | 10-08 |
|  2 | Khilan   |   1500 | 10-08 |
|  2 | Khilan   |   1560 | 11-20 |

sql

TOP clause

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.
Note: All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.

Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
Read more: SQL - TOP, LIMIT or ROWNUM Clause

TOP-clause ROWNUM

sql

SELECT TOP example

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT TOP 3 * FROM CUSTOMERS;
This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
Read more: SQL - TOP, LIMIT or ROWNUM Clause

SQL-server

sql

SELECT LIMIT example

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
Following is an example on MySQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT * FROM CUSTOMERS
LIMIT 3;
This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
Read more: SQL - TOP, LIMIT or ROWNUM Clause

MySQL limit-keyword

sql

SELECT ROWNUM example

Example:

Consider the CUSTOMERS table having the following records:

| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
Following is an example on Oracle server, which would fetch top 3 records from CUSTOMERS table:
SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;
This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
Read more: SQL - TOP, LIMIT or ROWNUM Clause

Oracle

sql

Operators in The WHERE Clause

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
Read more: SQL WHERE Clause

WHERE-clause operators

sql

The HAVING Clause

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

HAVING-clause GROUP-BY-clause aggregate-functions

sql

SQL HAVING Example

SQL HAVING Example


|OrderID |EmployeeID|ShipperID  |
+--------+----------+-----------+
|10248	 | 5        |	3       |
+--------+----------+-----------+
|10249	 | 6        |	1       |
+--------+----------+-----------+
|10250	 | 4        |	2       |
And a selection from the "Employees" table:

|EmployeeID |LastName |
+-----------+---------+
|1          |Davolio  |
+-----------+---------+
|2          |Fuller   |
+-----------+---------+
|3          |Leverling|
Now we want to find if any of the employees has registered more than 10 orders.
We use the following SQL statement:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
Read more: SQL HAVING Clause

COUNT-Function

Loading rules...