re: What I learned from only using Select in PostgreSQL VIEW POST


re: Materialized view. Maybe this is what my manager meant.
I could see if you had a huuuggggee database maybe it's not worth the time searching through everything to create a view.
They had said it wasn't worth creating a view unless I were to use it all the time or it would slow everything down.

I suppose I was compelled to use group by as I'm a visual person and seeing all similarities in a table is more satisfying and easy to comprehend than individual records.

Thank you for your comprehensive response by the way. I've not heard v much about Hbase or Cassandra.

Think of views as a way to cut down on copy+paste. There's no point in creating them for one-off queries, but if you have a few:

FROM customers c
JOIN orders o ON o.customer_id =
JOIN order_products op ON op.order_id =
JOIN products p ON = op.product_id;
SELECT,, COUNT(DISTINCT AS countries_shipped_to
FROM products p
JOIN order_products op ON op.product_id =
JOIN orders o ON = op.order_id
ORDER BY countries_shipped_to DESC;

Both of these queries use the same orders-order_products-products information. They do it in a different order since the first is starting from customers and the second from products itself, but it's the same data. Instead of joining those three tables, you could create a view with the query:

SELECT o.*, p.*
FROM orders o
JOIN order_products op ON op.order_id =
JOIN products p ON = op.product_id;

Then you can use the view in your queries and suddenly they're a good bit simpler -- the complexity of the order-to-product relationship is encapsulated in the view, so your queries don't have to manage it themselves.

code of conduct - report abuse