Your syntax (without
backticks) will work on
See SQL at the bottom of the post.
(tl;dr) The answer to the question "MySQL: Do you need to use backticks on column names when using the dot to refer to a table name" is "No, you do not need to use backticks!".
However, I beg of you, if you value your own sanity, do NOT EVER use SQL keywords.
TYPE are such words. It makes debugging a nightmare and is completely unnecessary anyway. Use identifiers such as
thing_type instead. Just because you can drive your car over a cliff, doesn't mean that you should!
Most (all?) modern
RDBMSs have (at least) 64 character(*) identifiers. That_is_more_than_enough_dont_you_think?
Furthermore (and very importantly) if you use the superfluous (and highly annoying)
backtick, you'll be bunched when cutting and pasting your SQL to different servers - my advice is to avoid at all costs. It's no longer required and is tricky to type!
Get some SQL naming conventions off the web (example), pick one and stick to it! This is somewhat of a religious topic (an "ecumenical matter" in the immortal words of Fr. Jack!). Read around and decide for yourself!
My own preference is for table names to be singular (a table is a collection anyway), SQL in UPPER CASE and identifiers_in_lower_case_with_underscores - makes reading easy!
You (and those who come after you) will be eternally grateful!
(*) from here, Oracle was 30 bytes up and including 12.1, but it's now 128 bytes - almost a tweet! :-)
Now, consider the following DDL, DML, query and response:
(simple, legible and portable!)
=========== DDL and DML ==================
CREATE TABLE table1
my_type VARCHAR (10),
type VARCHAR (10)
INSERT INTO table1 VALUES
(1, 'type1', 34, 'type1_bis'),
(2, 'type2', 23, 'type2_bis'),
(3, 'type3', 56, 'type3_bis'),
(4, 'type4', 78, 'type4_bis'),
(5, 'type5', 90, 'type5_bis');
And a simple query:
t.my_count, -- see second MySQL sample!
my_count my_type count type
1 type1 34 type1_bis
2 type2 23 type2_bis
3 type3 56 type3_bis
4 type4 78 type4_bis
5 type5 90 type5_bis