Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 280 of 547
How can we subtract values in MySQL table with the help of LEFT JOIN?
It can be understood with the help of an example in which two tables are having some values and we subtract the values with the help of LEFT JOIN. Here we are taking two tables having the following data −mysql> Select * from value_curdate; +----+----------+-------+ | Id | Product | Price | +----+----------+-------+ | 1 | Notebook | 100 | | 2 | Pen | 40 | | 3 | Pencil | 65 | +----+----------+-------+ 3 rows in set (0.00 sec) ...
Read MoreHow can INTERSECTION between tables be implemented with the help of MySQL joins?
Actually, INTERSECTION is just an inner join on all columns. We are taking a simple example of two tables, having the data as follows −mysql> Select * from value1; +------+------+ | i | j | +------+------+ | 1 | 1 | | 2 | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i | j | +------+------+ | 1 | 1 | | 3 | 3 | +------+------+ 2 rows in set (0.00 sec)Now, the following query will do the INTERSECTION between these tables −mysql> Select * from value1 join value2 using(i,j); +------+------+ | i | j | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.08 sec)
Read MoreHow DIFFERENCES between tables can be implemented with the help of MySQL joins?
We can get the differences between the tables by unioning exclusion joins from 1st table to 2nd table and from 2nd table to 1st table. To understand it, we are taking the example of following two tables −mysql> Select * from value1; +-----+-----+ | i | j | +-----+-----+ | 1 | 1 | | 2 | 2 | +-----+-----+ 2 rows in set (0.00 sec) mysql> Select * from value2; +------+------+ | i | j | +------+------+ | ...
Read MoreHow can we distinguish between MySQL CROSS JOIN and INNER JOIN?
We can distinguish between MySQL CROSS JOIN and INNER JOIN only on the basis of join-predicate i.e. the condition specified. While writing the query for INNER JOIN we need to specify the condition but in contrast, we do not need to specify the condition while writing a query for CROSS JOIN. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data −mysql> Select * from tbl_1; +----+--------+ | Id | Name | +----+--------+ | 1 | Gaurav | | 2 | Rahul | | 3 | ...
Read MoreWhat MySQL returns if I do not use the keyword 'RIGHT' or 'LEFT' while writing the query for RIGHT JOIN or LEFT JOIN?
In both the cases i.e. on not using ‘RIGHT’ or ‘LEFT’ keyword in the query, MySQL will return the result by taking it as INNER JOIN query. It is because the only difference between RIGHT, LEFT and INNER JOIN is the keyword of RIGHT or LEFT. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data −mysql> Select * from tbl_1; +----+--------+ | Id | Name | +----+--------+ | 1 | Gaurav | | 2 | Rahul | | 3 | Raman | ...
Read MoreIn MySQL, how can we maintain data-driven table relationship using joins?
Actually, sometimes we can avoid data-driven relationships in tables and we need to join them. It can be done with the help of CASE statement in the SELECT list to handle the joining possibilities. To understand it, we are taking the example of three data-driven tables namely ‘Student_Detail’ which have the following data −mysql> Select * from student_detail; +----+---------+ | Id | Name | +----+---------+ | 1 | Harshit | | 2 | Rahul | | 3 | Aarav | +----+---------+ 3 rows in set (0.00 sec)Now, we have the ...
Read MoreWhat is the similarity between prepared statements and MySQL user variables?
As we know that MySQL user variables are specific to client connection within which they are used and exist only for the duration of that connection. When a connection ends, all its user variables are lost. Similarly, the prepared statements also exist only for the duration of the session in which it is created and it is visible to the session in which it is created. When a session ends, all the prepared statements for that session are discarded.Another similarity is that prepared statements are also not case-sensitive like MySQL user variables. For example, stmt11 and STMT11 both are same ...
Read MoreWhat happens if I will prepare the statement with the same name without de-allocating the earlier one?
Actually, in MySQL, we can prepare a statement with the same name without de-allocating the earlier one because MySQL automatically drops the prepared statements when they are redefined or when we close the connection to the server. In other words, we can say that we can use the same name for prepared statements without de-allocating them explicitly. But, to free the memory on the server side we must have to de-allocate them. It can be done with the help of DEALLOCATE statement as follows −DEALLOCATE PREPARE statement;Here statement is the name of the prepared statements.DROP PREPARE statements is the synonym ...
Read MoreWhat kind of SQL statements can be used to prepare statements?
Actually, it is not possible to prepare all SQL statements because MySQL only allows the following kinds of SQL statements that can be prepared:SELECT statementsExamplemysql> PREPARE stmt FROM 'SELECT tender_value from Tender WHERE Companyname = ?'; Query OK, 0 rows affected (0.09 sec) Statement prepared mysql> SET @A = 'Singla Group.'; Query OK, 0 rows affected (0.00 sec) mysql> EXECUTE stmt using @A; +--------------+ | tender_value | +--------------+ | 220.255997 | +--------------+ 1 row in set (0.07 sec) mysql> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.00 sec)INSERT, REPLACE, UPDATE and DELETEstatements that modify the ...
Read MoreHow Are MySQL INSTR() and LIKE operator similar?
We can use both INSTR() function and LIKE operator to search or match a particular pattern and they return same result. It can be demonstrated from the following example of ‘Student’ table.ExampleSuppose we want to search name, which contains ‘av’ in it, from ‘Student’ table. We can use INSTR() function as follows −mysql> Select Name from student where INSTR(name, 'av') > 0; +--------+ | Name | +--------+ | Gaurav | | Aarav | | Gaurav | +--------+ 3 rows in set (0.00 sec)Now, for the same kind of search we can use LIKE operator as follows −mysql> Select Name ...
Read More