I'm doing databases in school now, but I couldn't wait to make one/learn basic SQL, so I made a database of the devices on the home network!

Shown here is the DB and an SQL query that was basically corrected by the program ;-;

  • 7
    Congrats on your achievement 😀
    Small advice, don't do SELECT *
    But instead use column names, because in production software using column names helps in:
    1. Reducing amount of data read from DB
    2. Assuming column name changed or even entire column is dropped error is detecged from database level
    3. Using columns instead of SELECT *
    Itwill give reader an idea of why this query was written instead of having to read code afterwards to find out
  • 1
    @gitpush thank you very much! :)
  • 3
    @gitpush 😥
    that star is evil when the code is using 2 fields, but table has 20.

    Also * will return endless columns when you get into joins or multi table joins and your query loses context really quickly.
  • 1
    I got distracted and only read "I couldn't wait to make one/learn basic" and I was like wut
  • 0
    @C0D4 "select a.* from a join b on .." and the problem is gone.

    For its use, it depends. If I only need 2/3, I get them by name, otherwise "*".

    @gitpush well, technically you'll need to check what they are doing with the results of the query anyway, so having 4 called by name or everyone doesn't make a big difference. In the end, I think it is just a matter of taste..
  • 1

    The * has it's use.

    If you are joining tables you should use (and need to) use the table alias.

    You can join several tables and just use database.table.* to get everything from the database.table

    The part where it becomes important to select specific fields is mostly in regards to data type and data type handling.

    If you have eg mostly numeric data inside the table it might not be relevant - but blob / text and other binary storage might lead to an performance impact...
    - due to the way it's stored (eg in MySQL InnoDB outside the "main table data" )
    - due to it's size - eg. one utf8 char has 4 byte or for binary storage in most cases it's stored in chunks / inodes… leading to Overhead
    - both cases might influence the query plan

    And.... To end it.
    COUNT(*) is on a table with a PK in all cases faster than COUNT(columnName) in MySQL.

    So. * is not evil per se. Just think about wether you make the database give you necessary information or If you make it.... Vomit.
  • 1
    SQL is a breeze to learn.
    It's also a breeze to write terrible, inefficient queries.
Add Comment