Even though databases are designed to avoid duplicate rows/records, one cannot avoid duplicates in the columns. We have real time datas which can have duplicate values. For example, in a BOOK_LOAN table same borrower can borrow same book copy multiple times. Only key that makes borrower record distinct from his other record is the loan date and due date. But if we check the columns BRWR_ID, ISBN and copy number for this borrower, we have duplicate values in these columns.
SELECT * FROM BOOK_LOAN;
SELECT BRWR_ID FROM BOOK_LOAN;
But above query gives whole list of borrowers. From this list we cannot find unique borrowers. If we eliminate duplicate borrower, then it will be the better result. This can be done by using DISTICT keyword. Below query gives distinct borrower IDs who have borrowed the books.
SELECT DISTINCT BRWR_ID FROM BOOK_LOAN;
But if we have to see borrower names, then we have to join it with BORROWER table. Then also one can write query with DISTINCT keyword to see distinct borrower names.
SELECT DISTINCT b.BRWR_NAME FROM BOOK_LOAN bl, BORROWER b WHERE bl.BRWR_ID = b.BRWR_ID;
If we need to show multiple columns from multiple tables with their distinct values, then that can also be done using DISTINCT keyword. Here distinct value is picked from the combination of columns.
SELECT DISTINCT bl.BRWR_ID, b.BRWR_NAME FROM BOOK_LOAN bl, BORROWER b WHERE bl.BRWR_ID = b.BRWR_ID;
SELECT DISTINCT BRWR_ID, ISBN FROM BOOK_LOAN ORDER BY 1;
