MySQL Views are Extremely Useful

A quick overview of using views in MySQL.

First, create our products table:

CREATE TABLE `products` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now create our users table:

CREATE TABLE `users` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Lastly, create our transaction table:

CREATE TABLE `transactions` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id_idx` (`product_id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

To query across these tables we would need to join them. A query such as:

SELECT  
    users.id AS UserId,
    users.name AS UserName,
    users.email AS UserEmail,
    products.id AS ProductId, 
    products.name AS ProductName, 
    products.price AS ProductPrice 
FROM transactions
LEFT JOIN users ON (users.id = transactions.user_id)
LEFT JOIN ON (products.id = transactions.product_id);

If we need to run this query often, we could simplify the process by creating a view of the above query. To create the view, we run the following SQL:

USE `my_database`;
CREATE VIEW view_transaction AS

SELECT

users.id AS UserId,

users.name AS UserName,

users.email AS UserEmail,

products.id AS ProductId,

products.name AS ProductName,

products.price AS ProductPrice
FROM transactions 

LEFT JOIN users ON (users.id = transactions.user_id) 

LEFT JOIN products ON (products.id = transactions.product_id);

Instead of then having to query against the large join, we could simply query the view:

SELECT * FROM view_transaction WHERE ProductPrice > 10