Bright Java Tutorial

is brought to you by DoITBright LLC

MySQL

Select Statement In MySQL

The purpose of SELECT statement is to retrieve existing data in a database table. For example, you found a website selling school and office supplies. You decided to register and you placed an order of 12 Piece Crayons. Then, you wish to retrieve the information of your order just to make sure everything looks good. Here is a typical way of how you will do it. First thing you will do is find out your account information. Usually an account id is what you will need.
  SELECT * FROM ACCOUNT;
                            
The above statement will retrieve all rows and columns from the table ACCOUNT. If you wish to retrieve only certain columns, in this case, ACCOUNT_ID and EMAIL will suffice. You will need to know the EMAIL for you to know which ACCOUNT_ID is associated to your account since you registered your own email address during the creation of your account. Here is what the SELECT statement will look like.
  SELECT ACCOUNT_ID, EMAIL FROM ACCOUNT;
                            
Assuming that your ACCOUNT_ID is equal to 100. The most efficient way of retrieving your account information is through the use of unique identifier of a database table. In this case, ACCOUNT_ID is the unique identifier of the ACCOUNT table since this is the PRIMARY KEY. Here is what your SELECT statement will look like.
  SELECT * FROM ACCOUNT WHERE ACCOUNT_ID = 100;
                            
If you wish to retrieve the information for ACCOUNT_ID 80 and 100 for some reason, your SELECT statement will look like this.
  SELECT * FROM ACCOUNT WHERE ACCOUNT_ID = 100 OR ACCOUNT_ID = 80;
                            
You can also do it this way.
  SELECT * FROM ACCOUNT WHERE ACCOUNT_ID IN (80,100);
                            
What if you do not know the ACCOUNT_ID but you know the EMAIL, how will you perform the query? Here is how.
  SELECT * FROM ACCOUNT WHERE EMAIL = 'customer1@testmail.com';
                            
Let us look into another use case. What if, you wish to view all your orders? It is now time to shift our focus to the other table we created in the previous tutorial, the ORDERED_ITEM table.
  SELECT * FROM ORDERED_ITEM WHERE ACCOUNT_ID = 100;
                            
Remember during the creation of the database table? We declared ACCOUNT_ID as a FOREIGN KEY that is associated with the ACCOUNT_ID of the ACCOUNT table. That means, we also have an ACCOUNT_ID column here in this table. The above SELECT statement will mean, retrieve all rows and columns from the ORDERED_ITEM table that has an ACCOUNT_ID = 100. If you wish to retrieve only the DESCRIPTION and ORDERED_ITEM_ID columns, here is the query.
  SELECT ORDERED_ITEM_ID, DESCRIPTION FROM ORDERED_ITEM WHERE ACCOUNT_ID = 100;
                            
It is simple when the use case is like this. You only needed to look into a single database table at a time to accomplish things. What if, you wanted to retrieve the ORDERED_ITEM_ID and DESCRIPTION of all the ordered items belonging to ACCOUNT_ID 80 and 100. You must also provide the ACCOUNT_ID and EMAIL of each row. Now this is a use case that needs to look into 2 tables simultaneously. Ok, basically, you are more concerned about the rows in the ORDERED_ITEM table. So, if you do not include the EMAIL, your query can be as simplke as this.
  SELECT ORDERED_ITEM_ID, DESCRIPTION, ACCOUNT_ID FROM ORDERED_ITEM WHERE ACCOUNT_ID IN (80, 100);
                            
If by any chance, ACCOUNT_ID = 80 ordered 2 items and ACCOUNT_ID = 100 ordered 1 item, The above query will return a total of 3 rows. Let us now work on retrieving the EMAIL together with the other info. You must understand that ORDERED_ITEM table is the parent table. The ACCOUNT table is joining table in this case. Here is how it will look like.
  SELECT o.ORDERED_ITEM_ID, o.DESCRIPTION, o.ACCOUNT_ID, a.EMAIL 
  FROM ORDERED_ITEM o JOIN ACCOUNT a ON o.ACCOUNT_ID = a.ACCOUNT_ID 
  WHERE o.ACCOUNT_ID IN (80, 100);
                            
You must have noticed that we have the character 'o' after the ORDERED_ITEM and 'a' after the ACCOUNT tables. This is like giving them an alias. So, in this case, if we want to say, I am referring to the column ACCOUNT_ID from ACCOUNT table, it will be a.ACCOUNT_ID. The above query should still result in 3 rows with 4 columns, namely, ORDERED_ITEM_ID, DESCRIPTION, ACCOUNT_ID and EMAIL. Take alook at this query.
  SELECT o.ORDERED_ITEM_ID, o.DESCRIPTION, o.ACCOUNT_ID, a.EMAIL 
  FROM ORDERED_ITEM o INNER JOIN ACCOUNT a ON o.ACCOUNT_ID = a.ACCOUNT_ID 
  WHERE o.ACCOUNT_ID IN (80, 100);
                            
The results is exactly the same. We retrieved 3 rows using the above query. The reason is, JOIN and INNER JOIN is exactly the same. The meaning of INNER JOIN is, both table, the parent and the child, should have data in that specific column matching the provided value. In our table design, ACCOUNT_ID is both present in ACCOUNT and ORDERED_ITEM tables because the ACCOUNT_ID in ORDERED_ITEM table is a FOREIGN KEY. This means, it is assured that all the ACCOUNT_ID in ORDERED_ITEM table should have an entry to the ACCOUNT table. No value of ACCOUNT_ID in ORDERED_ITEM shall be non existing in the ACCOUNT table. Let us just say, for some reason, during the creation of ORDERED_ITEM table, we did not declare ACCOUNT_ID as FOREIGN KEY. This means, we could put any value in the ACCOUNT_ID column that may or may not exist in ACCOUNT table. If this happens, it will not retrieve those rows that have non existing in the other table.


Back